{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "171a4221-bcc9-4157-a13b-bbeedb9ccb72",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "数据生成完成！共生成：\n",
      "- 用户表：200 条\n",
      "- 消费表：1194 条\n",
      "- 产品表：50 条\n"
     ]
    }
   ],
   "source": [
    "from faker import Faker\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from datetime import datetime, timedelta\n",
    "\n",
    "# 初始化Faker\n",
    "fake = Faker('zh_CN')\n",
    "Faker.seed(42)  # 固定随机种子，保证数据可复现\n",
    "\n",
    "# 1. 生成用户基础表（200条，避免用户重复）\n",
    "user_data = []\n",
    "for user_id in range(1, 201):\n",
    "    user_data.append({\n",
    "        'user_id': user_id,\n",
    "        'name': fake.name(),\n",
    "        'gender': np.random.choice(['男', '女'], p=[0.52, 0.48]),\n",
    "        'age': np.random.randint(18, 65),\n",
    "        'city': fake.city(),\n",
    "        'register_time': fake.date_between(start_date='-3y', end_date='today')\n",
    "    })\n",
    "user_df = pd.DataFrame(user_data)\n",
    "\n",
    "# 2. 生成消费行为表（1000+条，关联用户ID）\n",
    "consume_data = []\n",
    "categories = ['电子产品', '服装鞋帽', '食品生鲜', '家居用品', '美妆护肤', '运动户外']\n",
    "payment_methods = ['微信支付', '支付宝', '银行卡支付', '现金']\n",
    "for _ in range(1200):  # 1200条消费记录\n",
    "    user_id = np.random.randint(1, 201)  # 关联已有用户\n",
    "    consume_data.append({\n",
    "        'consume_id': _ + 1,\n",
    "        'user_id': user_id,\n",
    "        'consume_amount': round(np.random.normal(500, 200), 2),  # 消费金额（正态分布）\n",
    "        'consume_category': np.random.choice(categories, p=[0.2, 0.25, 0.2, 0.15, 0.1, 0.1]),\n",
    "        'payment_method': np.random.choice(payment_methods, p=[0.45, 0.4, 0.1, 0.05]),\n",
    "        'consume_time': fake.date_between(start_date='-1y', end_date='today')\n",
    "    })\n",
    "consume_df = pd.DataFrame(consume_data)\n",
    "# 过滤异常值（消费金额为正）\n",
    "consume_df = consume_df[consume_df['consume_amount'] > 0]\n",
    "\n",
    "# 3. 生成产品信息表（50条，关联消费品类）\n",
    "product_data = []\n",
    "for product_id in range(1, 51):\n",
    "    category = np.random.choice(categories)\n",
    "    product_data.append({\n",
    "        'product_id': product_id,\n",
    "        'product_name': fake.word() + fake.word(),  # 生成简单产品名\n",
    "        'category': category,\n",
    "        'price': round(np.random.normal(300, 150), 2) if category != '食品生鲜' else round(np.random.normal(50, 30), 2),\n",
    "        'stock': np.random.randint(10, 200),\n",
    "        'department': '线上部门' if np.random.random() > 0.3 else '线下部门'\n",
    "    })\n",
    "product_df = pd.DataFrame(product_data)\n",
    "\n",
    "# 保存数据到Excel（便于后续查看）\n",
    "with pd.ExcelWriter('simulated_data.xlsx', engine='openpyxl') as writer:\n",
    "    user_df.to_excel(writer, sheet_name='用户表', index=False)\n",
    "    consume_df.to_excel(writer, sheet_name='消费表', index=False)\n",
    "    product_df.to_excel(writer, sheet_name='产品表', index=False)\n",
    "\n",
    "print(\"数据生成完成！共生成：\")\n",
    "print(f\"- 用户表：{len(user_df)} 条\")\n",
    "print(f\"- 消费表：{len(consume_df)} 条\")\n",
    "print(f\"- 产品表：{len(product_df)} 条\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "4ea3f0cf-9808-4265-8305-55f9a244a8d8",
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "plt.rcParams['font.sans-serif'] = ['SimHei']  # 解决中文显示\n",
    "plt.rcParams['axes.unicode_minus'] = False\n",
    "\n",
    "# 计算各品类总消费金额\n",
    "category_amount = consume_df.groupby('consume_category')['consume_amount'].sum().sort_values(ascending=False)\n",
    "\n",
    "# 绘制柱状图\n",
    "plt.figure(figsize=(10, 6))\n",
    "bars = plt.bar(category_amount.index, category_amount.values, color='#3498db')\n",
    "plt.title('各消费品类总金额分布', fontsize=14, fontweight='bold')\n",
    "plt.xlabel('消费品类', fontsize=12)\n",
    "plt.ylabel('总消费金额（元）', fontsize=12)\n",
    "# 在柱子上添加数值标签\n",
    "for bar in bars:\n",
    "    height = bar.get_height()\n",
    "    plt.text(bar.get_x() + bar.get_width()/2., height + 1000,\n",
    "             f'{int(height)}', ha='center', va='bottom', fontsize=10)\n",
    "plt.xticks(rotation=45)\n",
    "plt.tight_layout()\n",
    "plt.savefig('1_柱状图_品类金额分布.png', dpi=300)\n",
    "plt.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "58b36209-6580-4dde-b293-0626ee3f100d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import seaborn as sns\n",
    "\n",
    "plt.figure(figsize=(10, 6))\n",
    "sns.histplot(user_df['age'], bins=12, kde=True, color='#e74c3c', edgecolor='black')\n",
    "plt.title('用户年龄分布直方图', fontsize=14, fontweight='bold')\n",
    "plt.xlabel('年龄', fontsize=12)\n",
    "plt.ylabel('用户数量', fontsize=12)\n",
    "plt.grid(axis='y', alpha=0.3)\n",
    "plt.tight_layout()\n",
    "plt.savefig('2_直方图_用户年龄分布.png', dpi=300)\n",
    "plt.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "0dea6501-b864-4ea5-b1f8-1dcd12dbcd8e",
   "metadata": {},
   "outputs": [],
   "source": [
    "payment_count = consume_df['payment_method'].value_counts()\n",
    "\n",
    "plt.figure(figsize=(8, 8))\n",
    "# 突出占比最高的支付方式\n",
    "explode = [0.1 if i == 0 else 0 for i in range(len(payment_count))]\n",
    "colors = ['#f39c12', '#2ecc71', '#9b59b6', '#1abc9c']\n",
    "wedges, texts, autotexts = plt.pie(\n",
    "    payment_count.values, \n",
    "    labels=payment_count.index, \n",
    "    autopct='%1.1f%%',\n",
    "    explode=explode,\n",
    "    colors=colors,\n",
    "    shadow=True,\n",
    "    startangle=90,\n",
    "    textprops={'fontsize': 11}\n",
    ")\n",
    "# 美化百分比文字\n",
    "for autotext in autotexts:\n",
    "    autotext.set_color('white')\n",
    "    autotext.set_fontweight('bold')\n",
    "plt.title('支付方式占比饼图', fontsize=14, fontweight='bold')\n",
    "plt.tight_layout()\n",
    "plt.savefig('3_饼图_支付方式占比.png', dpi=300)\n",
    "plt.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "152c51f0-503b-4820-be63-326c35bd92a3",
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.figure(figsize=(12, 7))\n",
    "# 核心修复：将 x 变量赋值给 hue，同时设置 legend=False（避免重复图例）\n",
    "sns.boxplot(\n",
    "    x='consume_category', \n",
    "    y='consume_amount', \n",
    "    data=consume_df, \n",
    "    palette='Set2',\n",
    "    hue='consume_category',  # 按 x 变量分组（和原逻辑一致）\n",
    "    legend=False  # 隐藏图例（因 x 已明确分类，无需重复）\n",
    ")\n",
    "plt.title('各消费品类金额分布箱线图', fontsize=14, fontweight='bold')\n",
    "plt.xlabel('消费品类', fontsize=12)\n",
    "plt.ylabel('消费金额（元）', fontsize=12)\n",
    "plt.xticks(rotation=45)  # 旋转 x 轴标签，避免重叠\n",
    "plt.grid(axis='y', alpha=0.3)  # 只显示 y 轴网格，提升可读性\n",
    "plt.tight_layout()  # 自动调整布局，防止标签被截断\n",
    "plt.savefig('4_箱线图_品类金额分布.png', dpi=300)  # 保存图片（高分辨率）\n",
    "\n",
    "plt.close()  # 关闭画布，释放内存"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "cc6bae45-db86-4822-814e-712e0e4f1539",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 关联用户表和消费表，获取性别信息\n",
    "user_consume = pd.merge(consume_df, user_df[['user_id', 'gender']], on='user_id')\n",
    "\n",
    "plt.figure(figsize=(10, 6))\n",
    "# 核心修复：x 与 hue 绑定，legend=False 避免多余图例\n",
    "sns.violinplot(\n",
    "    x='gender', \n",
    "    y='consume_amount', \n",
    "    data=user_consume, \n",
    "    palette='Set3',\n",
    "    inner='quartile',  # 保留原逻辑：显示四分位数\n",
    "    hue='gender',      # 按性别分组（与 x 一致，匹配 palette 配色）\n",
    "    legend=False       # 隐藏图例（x 轴已明确性别分类，无需重复）\n",
    ")\n",
    "plt.title('不同性别消费金额分布小提琴图', fontsize=14, fontweight='bold')\n",
    "plt.xlabel('性别', fontsize=12)\n",
    "plt.ylabel('消费金额（元）', fontsize=12)\n",
    "plt.grid(axis='y', alpha=0.3)\n",
    "plt.tight_layout()\n",
    "plt.savefig('5_小提琴图_性别消费分布.png', dpi=300)\n",
    "plt.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "474a3dc5-2582-45dc-b97f-ebd548d2730a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 处理时间格式，提取年月\n",
    "consume_df['consume_month'] = pd.to_datetime(consume_df['consume_time']).dt.to_period('M')\n",
    "monthly_amount = consume_df.groupby('consume_month')['consume_amount'].sum()\n",
    "# 转换为datetime格式便于绘图\n",
    "monthly_amount.index = monthly_amount.index.to_timestamp()\n",
    "\n",
    "plt.figure(figsize=(14, 7))\n",
    "plt.plot(monthly_amount.index, monthly_amount.values, color='#e67e22', linewidth=2.5, marker='o', markersize=6)\n",
    "plt.title('月度消费金额趋势折线图', fontsize=14, fontweight='bold')\n",
    "plt.xlabel('月份', fontsize=12)\n",
    "plt.ylabel('总消费金额（元）', fontsize=12)\n",
    "plt.grid(True, alpha=0.3)\n",
    "plt.xticks(rotation=45)\n",
    "# 添加趋势线\n",
    "z = np.polyfit(range(len(monthly_amount)), monthly_amount.values, 1)\n",
    "p = np.poly1d(z)\n",
    "plt.plot(monthly_amount.index, p(range(len(monthly_amount))), \"--\", color='#c0392b', alpha=0.8, label='趋势线')\n",
    "plt.legend()\n",
    "plt.tight_layout()\n",
    "plt.savefig('6_折线图_月度消费趋势.png', dpi=300)\n",
    "plt.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "f4be6d31-70a5-436c-9a58-8c47191bef7b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 计算月度各品类消费金额\n",
    "monthly_category = consume_df.groupby(['consume_month', 'consume_category'])['consume_amount'].sum().unstack(fill_value=0)\n",
    "monthly_category.index = monthly_category.index.to_timestamp()\n",
    "\n",
    "plt.figure(figsize=(14, 8))\n",
    "plt.stackplot(\n",
    "    monthly_category.index, \n",
    "    monthly_category.T, \n",
    "    labels=monthly_category.columns,\n",
    "    alpha=0.8\n",
    ")\n",
    "plt.title('月度各品类消费金额面积图', fontsize=14, fontweight='bold')\n",
    "plt.xlabel('月份', fontsize=12)\n",
    "plt.ylabel('消费金额（元）', fontsize=12)\n",
    "plt.grid(axis='y', alpha=0.3)\n",
    "plt.legend(loc='upper left', fontsize=10)\n",
    "plt.xticks(rotation=45)\n",
    "plt.tight_layout()\n",
    "plt.savefig('7_面积图_月度品类趋势.png', dpi=300)\n",
    "plt.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "fc70296f-6ef6-42d0-917f-09b1db912ee6",
   "metadata": {},
   "outputs": [],
   "source": [
    "monthly_order = consume_df.groupby('consume_month').size()  # 月度订单数\n",
    "monthly_order.index = monthly_order.index.to_timestamp()\n",
    "\n",
    "fig, ax1 = plt.subplots(figsize=(14, 7))\n",
    "\n",
    "# 左轴：消费金额\n",
    "color1 = '#3498db'\n",
    "ax1.set_xlabel('月份', fontsize=12)\n",
    "ax1.set_ylabel('总消费金额（元）', color=color1, fontsize=12)\n",
    "ax1.plot(monthly_amount.index, monthly_amount.values, color=color1, linewidth=2.5, marker='o')\n",
    "ax1.tick_params(axis='y', labelcolor=color1)\n",
    "ax1.grid(True, alpha=0.3)\n",
    "\n",
    "# 右轴：订单数\n",
    "ax2 = ax1.twinx()\n",
    "color2 = '#e74c3c'\n",
    "ax2.set_ylabel('订单数', color=color2, fontsize=12)\n",
    "ax2.plot(monthly_order.index, monthly_order.values, color=color2, linewidth=2.5, marker='s')\n",
    "ax2.tick_params(axis='y', labelcolor=color2)\n",
    "\n",
    "plt.title('月度消费金额与订单数双轴折线图', fontsize=14, fontweight='bold')\n",
    "plt.xticks(rotation=45)\n",
    "plt.tight_layout()\n",
    "plt.savefig('8_双轴折线图_金额订单数.png', dpi=300)\n",
    "plt.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "b780c052-970a-49d3-acce-a955fd93f503",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 按注册时间排序，计算累计用户数\n",
    "user_df['register_time'] = pd.to_datetime(user_df['register_time'])\n",
    "daily_register = user_df.groupby(user_df['register_time'].dt.date).size().cumsum()\n",
    "\n",
    "plt.figure(figsize=(14, 7))\n",
    "plt.step(daily_register.index, daily_register.values, color='#27ae60', linewidth=2, where='mid', marker='.')\n",
    "plt.title('用户注册累计趋势阶梯图', fontsize=14, fontweight='bold')\n",
    "plt.xlabel('注册日期', fontsize=12)\n",
    "plt.ylabel('累计注册用户数', fontsize=12)\n",
    "plt.grid(True, alpha=0.3)\n",
    "plt.xticks(rotation=45)\n",
    "plt.tight_layout()\n",
    "plt.savefig('9_阶梯图_累计注册趋势.png', dpi=300)\n",
    "plt.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "76ff1b97-2fc9-4fa9-8656-c419c5617015",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "date列数据类型： object\n",
      "前5行数据：\n",
      "         date  year  month  day   amount\n",
      "0  2024-11-23  2024     11   23   647.79\n",
      "1  2024-11-24  2024     11   24   934.12\n",
      "2  2024-11-25  2024     11   25  1415.07\n",
      "3  2024-11-26  2024     11   26   797.91\n",
      "4  2024-11-27  2024     11   27   955.00\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "import calendar\n",
    "from datetime import datetime  # 导入datetime模块，用于提取日期信息\n",
    "\n",
    "# -------------------------- 第一步：确保consume_time是datetime类型（核心基础） --------------------------\n",
    "# 转换consume_time为datetime（根据实际格式调整format，若不确定可先注释format参数自动识别）\n",
    "# 示例格式：如果是\"2025-11-24\"用format='%Y-%m-%d'，如果是\"2025/11/24 14:30\"用format='%Y/%m/%d %H:%M'\n",
    "consume_df['consume_time'] = pd.to_datetime(\n",
    "    consume_df['consume_time'],\n",
    "    format='%Y-%m-%d',  # 请根据你的实际日期格式修改！不确定就删掉这行\n",
    "    errors='coerce'  # 无法转换的日期设为NaN\n",
    ")\n",
    "\n",
    "# 删除无效日期数据（避免后续报错）\n",
    "consume_df = consume_df.dropna(subset=['consume_time'])\n",
    "\n",
    "# -------------------------- 第二步：计算每日消费金额（优化分组方式） --------------------------\n",
    "# 直接用.dt.date分组（返回的是date类型），但后续提取年/月/日改用datetime模块\n",
    "daily_amount = consume_df.groupby(consume_df['consume_time'].dt.date)['consume_amount'].sum()\n",
    "\n",
    "# 转换为DataFrame\n",
    "daily_amount_df = daily_amount.reset_index()\n",
    "daily_amount_df.columns = ['date', 'amount']  # date列此时是date类型\n",
    "\n",
    "# -------------------------- 核心修复：提取年/月/日（兼容date类型） --------------------------\n",
    "# 用datetime模块的year/month/day属性提取，避免.dt访问器报错\n",
    "daily_amount_df['year'] = [d.year for d in daily_amount_df['date']]\n",
    "daily_amount_df['month'] = [d.month for d in daily_amount_df['date']]\n",
    "daily_amount_df['day'] = [d.day for d in daily_amount_df['date']]\n",
    "\n",
    "# -------------------------- 选择最近一个完整月（逻辑不变，确保数据有效） --------------------------\n",
    "month_days = daily_amount_df.groupby(['year', 'month'])['day'].nunique()\n",
    "complete_months = []\n",
    "for (year, month), days in month_days.items():\n",
    "    if days == calendar.monthrange(year, month)[1]:\n",
    "        complete_months.append((year, month))\n",
    "\n",
    "# 取最新的完整月（无则取最新月）\n",
    "if complete_months:\n",
    "    latest_year, latest_month = max(complete_months)\n",
    "else:\n",
    "    latest_year = daily_amount_df['year'].max()\n",
    "    latest_month = daily_amount_df[daily_amount_df['year'] == latest_year]['month'].max()\n",
    "\n",
    "month_data = daily_amount_df[(daily_amount_df['year'] == latest_year) & (daily_amount_df['month'] == latest_month)]\n",
    "\n",
    "# -------------------------- 填充日历网格（修复缩进后逻辑不变） --------------------------\n",
    "cal = calendar.monthcalendar(latest_year, latest_month)\n",
    "amount_grid = []\n",
    "for week in cal:\n",
    "    week_amount = []\n",
    "    for day in week:  # 确保缩进正确：在week循环内部\n",
    "        if day == 0:\n",
    "            week_amount.append(0)\n",
    "        else:\n",
    "            # 匹配该日金额，无数据则为0\n",
    "            day_amount = month_data[month_data['day'] == day]['amount'].sum()\n",
    "            week_amount.append(day_amount)\n",
    "    amount_grid.append(week_amount)\n",
    "\n",
    "# -------------------------- 绘制热力图（保持原效果） --------------------------\n",
    "plt.figure(figsize=(12, 6))\n",
    "sns.heatmap(\n",
    "    amount_grid,\n",
    "    cmap='YlOrRd',\n",
    "    annot=True,\n",
    "    fmt='.0f',  # 整数显示，需要小数可改为'.1f'\n",
    "    cbar_kws={'label': '消费金额（元）'},\n",
    "    xticklabels=['周一', '周二', '周三', '周四', '周五', '周六', '周日'],\n",
    "    yticklabels=[f'第{week+1}周' for week in range(len(cal))]\n",
    ")\n",
    "plt.title(f'{latest_year}年{latest_month}月每日消费金额日历热力图', fontsize=14, fontweight='bold')\n",
    "plt.tight_layout()\n",
    "plt.savefig('10_日历热力图_每日消费.png', dpi=300)\n",
    "plt.close()\n",
    "\n",
    "# 可选：打印数据类型和前5行，验证是否正确\n",
    "print(\"date列数据类型：\", daily_amount_df['date'].dtype)\n",
    "print(\"前5行数据：\")\n",
    "print(daily_amount_df[['date', 'year', 'month', 'day', 'amount']].head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "9ac137b7-754c-4792-a38b-f6ea68439756",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 计算不同性别各品类的平均消费金额\n",
    "gender_category = user_consume.groupby(['gender', 'consume_category'])['consume_amount'].mean().unstack(fill_value=0)\n",
    "\n",
    "# 绘制分组柱状图\n",
    "x = np.arange(len(gender_category.columns))\n",
    "width = 0.35  # 柱子宽度\n",
    "\n",
    "plt.figure(figsize=(12, 7))\n",
    "bars1 = plt.bar(x - width/2, gender_category.loc['男'], width, label='男性', color='#3498db')\n",
    "bars2 = plt.bar(x + width/2, gender_category.loc['女'], width, label='女性', color='#e74c3c')\n",
    "\n",
    "plt.title('不同性别各消费品类平均金额对比', fontsize=14, fontweight='bold')\n",
    "plt.xlabel('消费品类', fontsize=12)\n",
    "plt.ylabel('平均消费金额（元）', fontsize=12)\n",
    "plt.xticks(x, gender_category.columns, rotation=45)\n",
    "plt.legend()\n",
    "plt.grid(axis='y', alpha=0.3)\n",
    "# 添加数值标签\n",
    "for bars in [bars1, bars2]:\n",
    "    for bar in bars:\n",
    "        height = bar.get_height()\n",
    "        plt.text(bar.get_x() + bar.get_width()/2., height + 5,\n",
    "                 f'{height:.0f}', ha='center', va='bottom', fontsize=9)\n",
    "plt.tight_layout()\n",
    "plt.savefig('11_分组柱状图_性别品类对比.png', dpi=300)\n",
    "plt.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "f2d7cb47-4cfd-412a-89f2-155325722a16",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Figure size 1400x700 with 0 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# 计算月度各支付方式消费金额\n",
    "monthly_payment = consume_df.groupby(['consume_month', 'payment_method'])['consume_amount'].sum().unstack(fill_value=0)\n",
    "monthly_payment.index = monthly_payment.index.to_timestamp()\n",
    "\n",
    "plt.figure(figsize=(14, 7))\n",
    "monthly_payment.plot(kind='bar', stacked=True, figsize=(14, 7), colormap='Set3')\n",
    "plt.title('月度各支付方式消费金额堆积柱状图', fontsize=14, fontweight='bold')\n",
    "plt.xlabel('月份', fontsize=12)\n",
    "plt.ylabel('消费金额（元）', fontsize=12)\n",
    "plt.legend(title='支付方式', bbox_to_anchor=(1.05, 1), loc='upper left')\n",
    "plt.grid(axis='y', alpha=0.3)\n",
    "plt.xticks(rotation=45)\n",
    "plt.tight_layout()\n",
    "plt.savefig('12_堆积柱状图_月度支付方式.png', dpi=300)\n",
    "plt.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "2c848c97-e52f-4829-b79f-808d781e8407",
   "metadata": {},
   "outputs": [],
   "source": [
    "from math import pi\n",
    "\n",
    "# 计算各品类的用户渗透率（消费过该品类的用户数/总用户数）\n",
    "category_user = user_consume.groupby('consume_category')['user_id'].nunique()\n",
    "category_penetration = (category_user / user_df['user_id'].nunique() * 100).round(2)\n",
    "\n",
    "# 雷达图参数设置\n",
    "categories = category_penetration.index.tolist()\n",
    "N = len(categories)\n",
    "# 计算每个类别的角度\n",
    "angles = [n / float(N) * 2 * pi for n in range(N)]\n",
    "angles += angles[:1]  # 闭合图形\n",
    "values = category_penetration.values.tolist()\n",
    "values += values[:1]  # 闭合图形\n",
    "\n",
    "plt.figure(figsize=(10, 10))\n",
    "ax = plt.subplot(111, polar=True)\n",
    "# 绘制折线\n",
    "ax.plot(angles, values, linewidth=2, linestyle='solid', color='#9b59b6')\n",
    "# 填充面积\n",
    "ax.fill(angles, values, alpha=0.4, color='#9b59b6')\n",
    "# 设置标签\n",
    "ax.set_xticks(angles[:-1])\n",
    "ax.set_xticklabels(categories, fontsize=11)\n",
    "# 设置y轴范围\n",
    "ax.set_ylim(0, 100)\n",
    "ax.set_yticks([20, 40, 60, 80, 100])\n",
    "ax.set_yticklabels(['20%', '40%', '60%', '80%', '100%'], fontsize=9)\n",
    "ax.set_title('各消费品类用户渗透率雷达图', fontsize=14, fontweight='bold', pad=20)\n",
    "plt.tight_layout()\n",
    "plt.savefig('13_雷达图_品类渗透率.png', dpi=300)\n",
    "plt.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "ca557fc4-7081-432f-a8cc-e22eaa570ec6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "数据合并成功，user_consume包含字段： ['consume_id', 'user_id', 'consume_amount', 'consume_category', 'payment_method', 'consume_time', 'city']\n",
      "\n",
      "前5个消费频次最高的城市：\n",
      "city\n",
      "欣市     26\n",
      "海口市    22\n",
      "惠州市    22\n",
      "璐县     20\n",
      "丽丽市    19\n",
      "Name: count, dtype: int64\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\29815\\AppData\\Local\\Temp\\ipykernel_19696\\3798590551.py:52: UserWarning: Glyph 21508 (\\N{CJK UNIFIED IDEOGRAPH-5404}) missing from font(s) DejaVu Sans.\n",
      "  plt.tight_layout()  # 自动调整布局，防止标题被截断\n",
      "C:\\Users\\29815\\AppData\\Local\\Temp\\ipykernel_19696\\3798590551.py:52: UserWarning: Glyph 22478 (\\N{CJK UNIFIED IDEOGRAPH-57CE}) missing from font(s) DejaVu Sans.\n",
      "  plt.tight_layout()  # 自动调整布局，防止标题被截断\n",
      "C:\\Users\\29815\\AppData\\Local\\Temp\\ipykernel_19696\\3798590551.py:52: UserWarning: Glyph 24066 (\\N{CJK UNIFIED IDEOGRAPH-5E02}) missing from font(s) DejaVu Sans.\n",
      "  plt.tight_layout()  # 自动调整布局，防止标题被截断\n",
      "C:\\Users\\29815\\AppData\\Local\\Temp\\ipykernel_19696\\3798590551.py:52: UserWarning: Glyph 28040 (\\N{CJK UNIFIED IDEOGRAPH-6D88}) missing from font(s) DejaVu Sans.\n",
      "  plt.tight_layout()  # 自动调整布局，防止标题被截断\n",
      "C:\\Users\\29815\\AppData\\Local\\Temp\\ipykernel_19696\\3798590551.py:52: UserWarning: Glyph 36153 (\\N{CJK UNIFIED IDEOGRAPH-8D39}) missing from font(s) DejaVu Sans.\n",
      "  plt.tight_layout()  # 自动调整布局，防止标题被截断\n",
      "C:\\Users\\29815\\AppData\\Local\\Temp\\ipykernel_19696\\3798590551.py:52: UserWarning: Glyph 39057 (\\N{CJK UNIFIED IDEOGRAPH-9891}) missing from font(s) DejaVu Sans.\n",
      "  plt.tight_layout()  # 自动调整布局，防止标题被截断\n",
      "C:\\Users\\29815\\AppData\\Local\\Temp\\ipykernel_19696\\3798590551.py:52: UserWarning: Glyph 27425 (\\N{CJK UNIFIED IDEOGRAPH-6B21}) missing from font(s) DejaVu Sans.\n",
      "  plt.tight_layout()  # 自动调整布局，防止标题被截断\n",
      "C:\\Users\\29815\\AppData\\Local\\Temp\\ipykernel_19696\\3798590551.py:52: UserWarning: Glyph 35789 (\\N{CJK UNIFIED IDEOGRAPH-8BCD}) missing from font(s) DejaVu Sans.\n",
      "  plt.tight_layout()  # 自动调整布局，防止标题被截断\n",
      "C:\\Users\\29815\\AppData\\Local\\Temp\\ipykernel_19696\\3798590551.py:52: UserWarning: Glyph 20113 (\\N{CJK UNIFIED IDEOGRAPH-4E91}) missing from font(s) DejaVu Sans.\n",
      "  plt.tight_layout()  # 自动调整布局，防止标题被截断\n",
      "C:\\Users\\29815\\AppData\\Local\\Temp\\ipykernel_19696\\3798590551.py:52: UserWarning: Glyph 22270 (\\N{CJK UNIFIED IDEOGRAPH-56FE}) missing from font(s) DejaVu Sans.\n",
      "  plt.tight_layout()  # 自动调整布局，防止标题被截断\n",
      "C:\\Users\\29815\\AppData\\Local\\Temp\\ipykernel_19696\\3798590551.py:53: UserWarning: Glyph 21508 (\\N{CJK UNIFIED IDEOGRAPH-5404}) missing from font(s) DejaVu Sans.\n",
      "  plt.savefig('14_城市消费频次词云图.png', dpi=300, bbox_inches='tight')  # 高分辨率保存\n",
      "C:\\Users\\29815\\AppData\\Local\\Temp\\ipykernel_19696\\3798590551.py:53: UserWarning: Glyph 22478 (\\N{CJK UNIFIED IDEOGRAPH-57CE}) missing from font(s) DejaVu Sans.\n",
      "  plt.savefig('14_城市消费频次词云图.png', dpi=300, bbox_inches='tight')  # 高分辨率保存\n",
      "C:\\Users\\29815\\AppData\\Local\\Temp\\ipykernel_19696\\3798590551.py:53: UserWarning: Glyph 24066 (\\N{CJK UNIFIED IDEOGRAPH-5E02}) missing from font(s) DejaVu Sans.\n",
      "  plt.savefig('14_城市消费频次词云图.png', dpi=300, bbox_inches='tight')  # 高分辨率保存\n",
      "C:\\Users\\29815\\AppData\\Local\\Temp\\ipykernel_19696\\3798590551.py:53: UserWarning: Glyph 28040 (\\N{CJK UNIFIED IDEOGRAPH-6D88}) missing from font(s) DejaVu Sans.\n",
      "  plt.savefig('14_城市消费频次词云图.png', dpi=300, bbox_inches='tight')  # 高分辨率保存\n",
      "C:\\Users\\29815\\AppData\\Local\\Temp\\ipykernel_19696\\3798590551.py:53: UserWarning: Glyph 36153 (\\N{CJK UNIFIED IDEOGRAPH-8D39}) missing from font(s) DejaVu Sans.\n",
      "  plt.savefig('14_城市消费频次词云图.png', dpi=300, bbox_inches='tight')  # 高分辨率保存\n",
      "C:\\Users\\29815\\AppData\\Local\\Temp\\ipykernel_19696\\3798590551.py:53: UserWarning: Glyph 39057 (\\N{CJK UNIFIED IDEOGRAPH-9891}) missing from font(s) DejaVu Sans.\n",
      "  plt.savefig('14_城市消费频次词云图.png', dpi=300, bbox_inches='tight')  # 高分辨率保存\n",
      "C:\\Users\\29815\\AppData\\Local\\Temp\\ipykernel_19696\\3798590551.py:53: UserWarning: Glyph 27425 (\\N{CJK UNIFIED IDEOGRAPH-6B21}) missing from font(s) DejaVu Sans.\n",
      "  plt.savefig('14_城市消费频次词云图.png', dpi=300, bbox_inches='tight')  # 高分辨率保存\n",
      "C:\\Users\\29815\\AppData\\Local\\Temp\\ipykernel_19696\\3798590551.py:53: UserWarning: Glyph 35789 (\\N{CJK UNIFIED IDEOGRAPH-8BCD}) missing from font(s) DejaVu Sans.\n",
      "  plt.savefig('14_城市消费频次词云图.png', dpi=300, bbox_inches='tight')  # 高分辨率保存\n",
      "C:\\Users\\29815\\AppData\\Local\\Temp\\ipykernel_19696\\3798590551.py:53: UserWarning: Glyph 20113 (\\N{CJK UNIFIED IDEOGRAPH-4E91}) missing from font(s) DejaVu Sans.\n",
      "  plt.savefig('14_城市消费频次词云图.png', dpi=300, bbox_inches='tight')  # 高分辨率保存\n",
      "C:\\Users\\29815\\AppData\\Local\\Temp\\ipykernel_19696\\3798590551.py:53: UserWarning: Glyph 22270 (\\N{CJK UNIFIED IDEOGRAPH-56FE}) missing from font(s) DejaVu Sans.\n",
      "  plt.savefig('14_城市消费频次词云图.png', dpi=300, bbox_inches='tight')  # 高分辨率保存\n"
     ]
    }
   ],
   "source": [
    "# 1. 导入必需库\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "from wordcloud import WordCloud\n",
    "\n",
    "# 2. 读取你之前生成的Excel数据（确保simulated_data.xlsx在当前路径）\n",
    "# 读取用户表和消费表\n",
    "user_df = pd.read_excel('simulated_data.xlsx', sheet_name='用户表')\n",
    "consume_df = pd.read_excel('simulated_data.xlsx', sheet_name='消费表')\n",
    "\n",
    "# 3. 关键：只合并一次，获取包含city字段的user_consume\n",
    "user_consume = pd.merge(\n",
    "    left=consume_df,                # 左表：消费记录\n",
    "    right=user_df[['user_id', 'city']],  # 右表：用户ID+城市（仅需这两个字段）\n",
    "    on='user_id',                   # 关联字段：用户ID\n",
    "    how='left'                      # 左连接：保留所有消费记录\n",
    ")\n",
    "\n",
    "# 4. 验证city字段是否存在（避免后续报错）\n",
    "if 'city' not in user_consume.columns:\n",
    "    raise ValueError(\"合并失败！请检查user_df是否包含'city'字段\")\n",
    "print(\"数据合并成功，user_consume包含字段：\", user_consume.columns.tolist())\n",
    "\n",
    "# 5. 统计城市消费频次（直接用已合并的user_consume，无需重复合并）\n",
    "city_consume_count = user_consume['city'].value_counts()  # 这一步是核心，直接用city字段\n",
    "print(\"\\n前5个消费频次最高的城市：\")\n",
    "print(city_consume_count.head())\n",
    "\n",
    "# 6. 生成词云文本（城市名重复次数=消费频次，限制最大50次）\n",
    "city_text = ' '.join([\n",
    "    city + ' ' for city, count in city_consume_count.items()\n",
    "    for _ in range(min(count, 50))  # 控制重复次数，避免单个城市字体过大\n",
    "])\n",
    "\n",
    "# 7. 绘制词云图（适配Windows系统字体）\n",
    "plt.figure(figsize=(12, 8))\n",
    "wordcloud = WordCloud(\n",
    "    width=800,\n",
    "    height=600,\n",
    "    background_color='white',\n",
    "    font_path='msyh.ttc',  # Windows默认字体，无需修改（其他系统参考之前说明）\n",
    "    max_words=100,          # 最多显示100个城市\n",
    "    relative_scaling=0.8,   # 词频对字体大小的影响权重\n",
    "    random_state=42,        # 固定布局，结果可复现\n",
    "    collocations=False      # 避免重复显示无效词组\n",
    ").generate(city_text)\n",
    "\n",
    "# 8. 显示并保存词云图\n",
    "plt.imshow(wordcloud, interpolation='bilinear')  # 让图片更平滑\n",
    "plt.axis('off')  # 隐藏坐标轴\n",
    "plt.title('14_各城市消费频次词云图', fontsize=14, fontweight='bold', pad=20)\n",
    "plt.tight_layout()  # 自动调整布局，防止标题被截断\n",
    "plt.savefig('14_城市消费频次词云图.png', dpi=300, bbox_inches='tight')  # 高分辨率保存\n",
    "plt.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "2e4b63cf-4542-4a8d-809a-d0dbf728ebd2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "数据准备完成！已包含所有所需字段： ['consume_category', 'payment_method', 'consume_amount']\n"
     ]
    },
    {
     "data": {
      "application/vnd.plotly.v1+json": {
       "config": {
        "plotlyServerURL": "https://plot.ly"
       },
       "data": [
        {
         "arrangement": "snap",
         "link": {
          "color": "#cccccc",
          "source": [
           0,
           0,
           0,
           0,
           1,
           1,
           1,
           1,
           2,
           2,
           2,
           2,
           3,
           3,
           3,
           3,
           4,
           4,
           4,
           4,
           5,
           5,
           5,
           5
          ],
          "target": [
           6,
           7,
           8,
           9,
           6,
           7,
           8,
           9,
           6,
           7,
           8,
           9,
           6,
           7,
           8,
           9,
           6,
           7,
           8,
           9,
           6,
           7,
           8,
           9
          ],
          "value": [
           44884.06,
           37907.03,
           2622.28,
           11932.61,
           75067.95,
           57995.46,
           7436.54,
           18209.71,
           42251.27,
           51164.37,
           5585.9,
           12951.76,
           28473.98,
           26994.67,
           4149.66,
           4224.93,
           23636.69,
           23827.079999999998,
           5270.02,
           4373.02,
           56042.87,
           49009.01,
           6312.39,
           12883.42
          ]
         },
         "node": {
          "color": [
           "#3498db",
           "#e74c3c",
           "#2ecc71",
           "#f39c12",
           "#9b59b6",
           "#1abc9c",
           "#95a5a6",
           "#e67e22",
           "#34495e",
           "#27ae60"
          ],
          "label": [
           "家居用品",
           "服装鞋帽",
           "电子产品",
           "美妆护肤",
           "运动户外",
           "食品生鲜",
           "微信支付",
           "支付宝",
           "现金",
           "银行卡支付"
          ],
          "line": {
           "color": "black",
           "width": 0.5
          },
          "pad": 15,
          "thickness": 20
         },
         "type": "sankey"
        }
       ],
       "layout": {
        "font": {
         "size": 12
        },
        "height": 600,
        "template": {
         "data": {
          "bar": [
           {
            "error_x": {
             "color": "#2a3f5f"
            },
            "error_y": {
             "color": "#2a3f5f"
            },
            "marker": {
             "line": {
              "color": "#E5ECF6",
              "width": 0.5
             },
             "pattern": {
              "fillmode": "overlay",
              "size": 10,
              "solidity": 0.2
             }
            },
            "type": "bar"
           }
          ],
          "barpolar": [
           {
            "marker": {
             "line": {
              "color": "#E5ECF6",
              "width": 0.5
             },
             "pattern": {
              "fillmode": "overlay",
              "size": 10,
              "solidity": 0.2
             }
            },
            "type": "barpolar"
           }
          ],
          "carpet": [
           {
            "aaxis": {
             "endlinecolor": "#2a3f5f",
             "gridcolor": "white",
             "linecolor": "white",
             "minorgridcolor": "white",
             "startlinecolor": "#2a3f5f"
            },
            "baxis": {
             "endlinecolor": "#2a3f5f",
             "gridcolor": "white",
             "linecolor": "white",
             "minorgridcolor": "white",
             "startlinecolor": "#2a3f5f"
            },
            "type": "carpet"
           }
          ],
          "choropleth": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "type": "choropleth"
           }
          ],
          "contour": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "contour"
           }
          ],
          "contourcarpet": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "type": "contourcarpet"
           }
          ],
          "heatmap": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "heatmap"
           }
          ],
          "histogram": [
           {
            "marker": {
             "pattern": {
              "fillmode": "overlay",
              "size": 10,
              "solidity": 0.2
             }
            },
            "type": "histogram"
           }
          ],
          "histogram2d": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "histogram2d"
           }
          ],
          "histogram2dcontour": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "histogram2dcontour"
           }
          ],
          "mesh3d": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "type": "mesh3d"
           }
          ],
          "parcoords": [
           {
            "line": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "parcoords"
           }
          ],
          "pie": [
           {
            "automargin": true,
            "type": "pie"
           }
          ],
          "scatter": [
           {
            "fillpattern": {
             "fillmode": "overlay",
             "size": 10,
             "solidity": 0.2
            },
            "type": "scatter"
           }
          ],
          "scatter3d": [
           {
            "line": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatter3d"
           }
          ],
          "scattercarpet": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattercarpet"
           }
          ],
          "scattergeo": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattergeo"
           }
          ],
          "scattergl": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattergl"
           }
          ],
          "scattermap": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattermap"
           }
          ],
          "scattermapbox": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattermapbox"
           }
          ],
          "scatterpolar": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatterpolar"
           }
          ],
          "scatterpolargl": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatterpolargl"
           }
          ],
          "scatterternary": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatterternary"
           }
          ],
          "surface": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "surface"
           }
          ],
          "table": [
           {
            "cells": {
             "fill": {
              "color": "#EBF0F8"
             },
             "line": {
              "color": "white"
             }
            },
            "header": {
             "fill": {
              "color": "#C8D4E3"
             },
             "line": {
              "color": "white"
             }
            },
            "type": "table"
           }
          ]
         },
         "layout": {
          "annotationdefaults": {
           "arrowcolor": "#2a3f5f",
           "arrowhead": 0,
           "arrowwidth": 1
          },
          "autotypenumbers": "strict",
          "coloraxis": {
           "colorbar": {
            "outlinewidth": 0,
            "ticks": ""
           }
          },
          "colorscale": {
           "diverging": [
            [
             0,
             "#8e0152"
            ],
            [
             0.1,
             "#c51b7d"
            ],
            [
             0.2,
             "#de77ae"
            ],
            [
             0.3,
             "#f1b6da"
            ],
            [
             0.4,
             "#fde0ef"
            ],
            [
             0.5,
             "#f7f7f7"
            ],
            [
             0.6,
             "#e6f5d0"
            ],
            [
             0.7,
             "#b8e186"
            ],
            [
             0.8,
             "#7fbc41"
            ],
            [
             0.9,
             "#4d9221"
            ],
            [
             1,
             "#276419"
            ]
           ],
           "sequential": [
            [
             0,
             "#0d0887"
            ],
            [
             0.1111111111111111,
             "#46039f"
            ],
            [
             0.2222222222222222,
             "#7201a8"
            ],
            [
             0.3333333333333333,
             "#9c179e"
            ],
            [
             0.4444444444444444,
             "#bd3786"
            ],
            [
             0.5555555555555556,
             "#d8576b"
            ],
            [
             0.6666666666666666,
             "#ed7953"
            ],
            [
             0.7777777777777778,
             "#fb9f3a"
            ],
            [
             0.8888888888888888,
             "#fdca26"
            ],
            [
             1,
             "#f0f921"
            ]
           ],
           "sequentialminus": [
            [
             0,
             "#0d0887"
            ],
            [
             0.1111111111111111,
             "#46039f"
            ],
            [
             0.2222222222222222,
             "#7201a8"
            ],
            [
             0.3333333333333333,
             "#9c179e"
            ],
            [
             0.4444444444444444,
             "#bd3786"
            ],
            [
             0.5555555555555556,
             "#d8576b"
            ],
            [
             0.6666666666666666,
             "#ed7953"
            ],
            [
             0.7777777777777778,
             "#fb9f3a"
            ],
            [
             0.8888888888888888,
             "#fdca26"
            ],
            [
             1,
             "#f0f921"
            ]
           ]
          },
          "colorway": [
           "#636efa",
           "#EF553B",
           "#00cc96",
           "#ab63fa",
           "#FFA15A",
           "#19d3f3",
           "#FF6692",
           "#B6E880",
           "#FF97FF",
           "#FECB52"
          ],
          "font": {
           "color": "#2a3f5f"
          },
          "geo": {
           "bgcolor": "white",
           "lakecolor": "white",
           "landcolor": "#E5ECF6",
           "showlakes": true,
           "showland": true,
           "subunitcolor": "white"
          },
          "hoverlabel": {
           "align": "left"
          },
          "hovermode": "closest",
          "mapbox": {
           "style": "light"
          },
          "paper_bgcolor": "white",
          "plot_bgcolor": "#E5ECF6",
          "polar": {
           "angularaxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           },
           "bgcolor": "#E5ECF6",
           "radialaxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           }
          },
          "scene": {
           "xaxis": {
            "backgroundcolor": "#E5ECF6",
            "gridcolor": "white",
            "gridwidth": 2,
            "linecolor": "white",
            "showbackground": true,
            "ticks": "",
            "zerolinecolor": "white"
           },
           "yaxis": {
            "backgroundcolor": "#E5ECF6",
            "gridcolor": "white",
            "gridwidth": 2,
            "linecolor": "white",
            "showbackground": true,
            "ticks": "",
            "zerolinecolor": "white"
           },
           "zaxis": {
            "backgroundcolor": "#E5ECF6",
            "gridcolor": "white",
            "gridwidth": 2,
            "linecolor": "white",
            "showbackground": true,
            "ticks": "",
            "zerolinecolor": "white"
           }
          },
          "shapedefaults": {
           "line": {
            "color": "#2a3f5f"
           }
          },
          "ternary": {
           "aaxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           },
           "baxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           },
           "bgcolor": "#E5ECF6",
           "caxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           }
          },
          "title": {
           "x": 0.05
          },
          "xaxis": {
           "automargin": true,
           "gridcolor": "white",
           "linecolor": "white",
           "ticks": "",
           "title": {
            "standoff": 15
           },
           "zerolinecolor": "white",
           "zerolinewidth": 2
          },
          "yaxis": {
           "automargin": true,
           "gridcolor": "white",
           "linecolor": "white",
           "ticks": "",
           "title": {
            "standoff": 15
           },
           "zerolinecolor": "white",
           "zerolinewidth": 2
          }
         }
        },
        "title": {
         "text": "消费品类-支付方式桑基图（金额流向）",
         "x": 0.5
        },
        "width": 1000
       }
      }
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "桑基图生成完成！已保存为：15_桑基图_品类支付流向.png\n"
     ]
    }
   ],
   "source": [
    "# 1. 导入必需库（确保已安装plotly：pip install plotly openpyxl）\n",
    "import pandas as pd\n",
    "import plotly.graph_objects as go\n",
    "\n",
    "# 2. 第一步：读取你之前生成的Excel数据（关键！获取基础数据表）\n",
    "# 读取消费表（含consume_category、payment_method字段）和用户表\n",
    "consume_df = pd.read_excel('simulated_data.xlsx', sheet_name='消费表')\n",
    "user_df = pd.read_excel('simulated_data.xlsx', sheet_name='用户表')\n",
    "\n",
    "# 3. 第二步：创建user_consume变量（表关联，获取完整数据）\n",
    "# 通过user_id合并两张表，生成包含所有所需字段的数据集\n",
    "user_consume = pd.merge(\n",
    "    left=consume_df,                # 左表：消费表（含品类、支付方式、金额）\n",
    "    right=user_df[['user_id', 'city']],  # 右表：用户表（仅需关联字段user_id，避免冗余）\n",
    "    on='user_id',                   # 关联关键字段：用户ID\n",
    "    how='left'                      # 左连接：保留所有消费记录\n",
    ")\n",
    "\n",
    "# 4. 验证关键字段是否存在（避免后续报错）\n",
    "required_fields = ['consume_category', 'payment_method', 'consume_amount']\n",
    "for field in required_fields:\n",
    "    if field not in user_consume.columns:\n",
    "        raise ValueError(f\"数据缺失关键字段：{field}，请检查消费表是否正确生成\")\n",
    "print(\"数据准备完成！已包含所有所需字段：\", required_fields)\n",
    "\n",
    "# 5. 第三步：计算桑基图所需的“品类-支付方式-金额”流量数据\n",
    "sankey_data = user_consume.groupby(\n",
    "    ['consume_category', 'payment_method']  # 按品类和支付方式分组\n",
    ")['consume_amount'].sum().reset_index()     # 汇总消费金额，重置索引为表格格式\n",
    "\n",
    "# 6. 定义桑基图的节点（左侧：消费品类，右侧：支付方式）\n",
    "categories = sankey_data['consume_category'].unique().tolist()  # 消费品类列表\n",
    "payments = sankey_data['payment_method'].unique().tolist()      # 支付方式列表\n",
    "labels = categories + payments  # 合并为完整节点列表（品类在前，支付方式在后）\n",
    "\n",
    "# 7. 定义桑基图的链接（source→target的流向关系）\n",
    "# source：品类在节点列表中的索引（左侧节点）\n",
    "source = [categories.index(row['consume_category']) for _, row in sankey_data.iterrows()]\n",
    "# target：支付方式在节点列表中的索引（右侧节点，需加上品类数量的偏移）\n",
    "target = [len(categories) + payments.index(row['payment_method']) for _, row in sankey_data.iterrows()]\n",
    "# value：流向的数值大小（即消费金额）\n",
    "value = sankey_data['consume_amount'].tolist()\n",
    "\n",
    "# 8. 绘制桑基图（设置样式和交互效果）\n",
    "fig = go.Figure(data=[go.Sankey(\n",
    "    arrangement=\"snap\",  # 节点自动对齐布局\n",
    "    node=dict(\n",
    "        pad=15,           # 节点之间的间距\n",
    "        thickness=20,     # 节点厚度\n",
    "        line=dict(color=\"black\", width=0.5),  # 节点边框\n",
    "        label=labels,     # 节点标签（品类+支付方式）\n",
    "        # 节点颜色（品类用一组色，支付方式用另一组色，区分左右）\n",
    "        color=['#3498db', '#e74c3c', '#2ecc71', '#f39c12', '#9b59b6', '#1abc9c'] + \n",
    "              ['#95a5a6', '#e67e22', '#34495e', '#27ae60']    \n",
    "    ),\n",
    "    link=dict(\n",
    "        source=source,  # 流向起点索引\n",
    "        target=target,  # 流向终点索引\n",
    "        value=value,    # 流向数值（金额）\n",
    "        color='#cccccc' # 流向线条颜色（灰色，突出节点颜色）\n",
    "    )\n",
    ")])\n",
    "\n",
    "# 9. 设置图表标题和尺寸，保存并显示\n",
    "fig.update_layout(\n",
    "    title_text=\"消费品类-支付方式桑基图（金额流向）\",  # 图表标题\n",
    "    font_size=12,          # 字体大小\n",
    "    width=1000,            # 图表宽度\n",
    "    height=600,            # 图表高度\n",
    "    title_x=0.5            # 标题水平居中\n",
    ")\n",
    "\n",
    "# 保存为图片（需安装kaleido：pip install kaleido）\n",
    "fig.write_image('15_桑基图_品类支付流向.png', scale=3)  # scale=3提升图片分辨率\n",
    "# 交互式显示（在Jupyter或浏览器中可hover查看具体数值）\n",
    "fig.show()\n",
    "\n",
    "print(\"\\n桑基图生成完成！已保存为：15_桑基图_品类支付流向.png\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "3f9c9810-8cf8-40f3-8e94-3c7bdd47cab0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "散点图生成完成！已解决中文显示和参数错误问题，文件保存为：16_散点图_年龄消费关联.png\n"
     ]
    }
   ],
   "source": [
    "# 1. 导入必需库\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import pandas as pd\n",
    "\n",
    "# 2. 关键：设置中文字体（解决中文显示乱码和Glyph缺失问题）\n",
    "plt.rcParams['font.sans-serif'] = ['Microsoft YaHei', 'SimHei', 'Arial Unicode MS']  # 兼容Windows/Mac\n",
    "plt.rcParams['axes.unicode_minus'] = False  # 解决负号显示异常\n",
    "\n",
    "# 3. 读取Excel数据（确保文件路径正确）\n",
    "user_df = pd.read_excel('simulated_data.xlsx', sheet_name='用户表')\n",
    "consume_df = pd.read_excel('simulated_data.xlsx', sheet_name='消费表')\n",
    "\n",
    "# 4. 合并数据生成user_consume（包含age、gender、consume_amount）\n",
    "user_consume = pd.merge(\n",
    "    left=consume_df,\n",
    "    right=user_df[['user_id', 'age', 'gender']],\n",
    "    on='user_id',\n",
    "    how='left'\n",
    ")\n",
    "\n",
    "# 5. 验证关键字段是否存在\n",
    "required_fields = ['age', 'consume_amount', 'gender']\n",
    "if not all(field in user_consume.columns for field in required_fields):\n",
    "    raise ValueError(f\"数据缺失必要字段！需包含：{required_fields}\")\n",
    "\n",
    "# 6. 绘制散点图（修复参数错误）\n",
    "plt.figure(figsize=(10, 7))\n",
    "\n",
    "# 6.1 绘制带性别区分的散点图\n",
    "sns.scatterplot(\n",
    "    x='age', \n",
    "    y='consume_amount', \n",
    "    data=user_consume,\n",
    "    hue='gender',        # 按性别区分颜色\n",
    "    style='gender',      # 按性别区分标记形状\n",
    "    s=80,                # 散点大小\n",
    "    alpha=0.7,           # 散点透明度（避免重叠遮挡）\n",
    "    palette={'男': '#3498db', '女': '#e74c3c'}  # 自定义性别颜色\n",
    ")\n",
    "\n",
    "# 6.2 添加年龄-消费金额趋势线\n",
    "sns.regplot(\n",
    "    x='age', \n",
    "    y='consume_amount', \n",
    "    data=user_consume,\n",
    "    scatter=False,               # 不重复显示散点\n",
    "    color='#2c3e50',             # 趋势线颜色（深灰色）\n",
    "    line_kws={'alpha': 0.8, 'linestyle': '--'}  # 趋势线样式（半透明虚线）\n",
    ")\n",
    "\n",
    "# 6.3 图表美化（修复legend参数）\n",
    "plt.title('用户年龄与消费金额关联散点图', fontsize=14, fontweight='bold', pad=20)\n",
    "plt.xlabel('年龄', fontsize=12, fontweight='500')\n",
    "plt.ylabel('消费金额（元）', fontsize=12, fontweight='500')\n",
    "plt.grid(True, alpha=0.3, linestyle='-', linewidth=0.5)  # 浅色网格线\n",
    "\n",
    "# 修复legend参数：用fontsize替代label_fontsize\n",
    "plt.legend(\n",
    "    title='性别', \n",
    "    title_fontsize=11,  # 图例标题字体大小（正确参数）\n",
    "    fontsize=10,        # 图例标签字体大小（正确参数，替代label_fontsize）\n",
    "    loc='upper right',  # 图例位置\n",
    "    frameon=True,       # 显示图例边框\n",
    "    fancybox=True,      # 圆角边框\n",
    "    shadow=True         # 阴影效果（提升美观度）\n",
    ")\n",
    "\n",
    "# 6.4 调整布局并保存图片\n",
    "plt.tight_layout()  # 自动调整布局，防止中文标签被截断\n",
    "plt.savefig(\n",
    "    '16_散点图_年龄消费关联.png', \n",
    "    dpi=300,                # 高分辨率（300dpi，适合印刷/报告）\n",
    "    bbox_inches='tight',    # 紧凑布局，避免边缘被裁剪\n",
    "    facecolor='white'       # 背景色为白色（避免透明背景）\n",
    ")\n",
    "plt.close()\n",
    "\n",
    "print(\"散点图生成完成！已解决中文显示和参数错误问题，文件保存为：16_散点图_年龄消费关联.png\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "90258882-5d9e-4c05-af09-679dc9776adb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "相关性分析数据形状： (9931, 5)\n",
      "缺失值统计：\n",
      "user_id           0\n",
      "age               0\n",
      "consume_amount    0\n",
      "price             0\n",
      "stock             0\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import pandas as pd\n",
    "\n",
    "# 2. 提前设置中文字体（解决中文显示问题）\n",
    "plt.rcParams['font.sans-serif'] = ['Microsoft YaHei', 'SimHei', 'Arial Unicode MS']\n",
    "plt.rcParams['axes.unicode_minus'] = False\n",
    "\n",
    "# 3. 关键：读取所有三张表（用户表、消费表、产品表）\n",
    "# 3.1 读取用户表（含user_id、age）\n",
    "user_df = pd.read_excel('simulated_data.xlsx', sheet_name='用户表')\n",
    "# 3.2 读取消费表（含user_id、consume_amount、consume_category）\n",
    "consume_df = pd.read_excel('simulated_data.xlsx', sheet_name='消费表')\n",
    "# 3.3 读取产品表（含category、price、stock）- 之前缺失的关键步骤\n",
    "product_df = pd.read_excel('simulated_data.xlsx', sheet_name='产品表')\n",
    "\n",
    "# 4. 第一步：合并用户表和消费表，生成user_consume\n",
    "user_consume = pd.merge(\n",
    "    left=consume_df,\n",
    "    right=user_df[['user_id', 'age']],  # 只取用户表的关键数值字段\n",
    "    on='user_id',\n",
    "    how='left'\n",
    ")\n",
    "\n",
    "# 5. 第二步：合并user_consume和产品表，生成完整的相关性分析数据\n",
    "# 注意：通过“消费品类（consume_category）”关联“产品品类（category）”\n",
    "corr_data = pd.merge(\n",
    "    left=user_consume,  # 左表：已包含user_id、age、consume_amount、consume_category\n",
    "    right=product_df[['category', 'price', 'stock']],  # 右表：产品表的数值字段\n",
    "    left_on='consume_category',  # 左表关联字段：消费记录的品类\n",
    "    right_on='category',  # 右表关联字段：产品的品类\n",
    "    how='left'  # 左连接：保留所有消费记录\n",
    ")\n",
    "\n",
    "# 6. 筛选数值型变量，删除重复值（避免数据冗余影响相关性）\n",
    "numeric_cols = ['user_id', 'age', 'consume_amount', 'price', 'stock']\n",
    "corr_data = corr_data[numeric_cols].drop_duplicates()\n",
    "\n",
    "# 7. 验证数据完整性（避免因缺失值导致相关性计算错误）\n",
    "print(\"相关性分析数据形状：\", corr_data.shape)\n",
    "print(\"缺失值统计：\")\n",
    "print(corr_data.isnull().sum())\n",
    "# 填充少量缺失值（若有）- 用均值填充数值型缺失值\n",
    "corr_data = corr_data.fillna(corr_data.mean())\n",
    "\n",
    "# 8. 计算相关系数矩阵并绘制热力图\n",
    "corr_matrix = corr_data.corr()\n",
    "\n",
    "plt.figure(figsize=(10, 8))\n",
    "sns.heatmap(\n",
    "    corr_matrix,\n",
    "    annot=True,          # 显示相关系数数值\n",
    "    fmt='.2f',           # 数值格式：保留2位小数\n",
    "    cmap='RdBu_r',       # 配色方案：红-蓝反向（红色负相关，蓝色正相关）\n",
    "    center=0,            # 颜色中心值（0，即无相关性）\n",
    "    square=True,         # 热力图单元格为正方形\n",
    "    linewidths=.5,       # 单元格边框宽度（分隔效果）\n",
    "    cbar_kws={'label': 'Pearson相关系数'}  # 颜色条标签\n",
    ")\n",
    "\n",
    "# 9. 图表美化\n",
    "plt.title('变量相关性分析热力图', fontsize=14, fontweight='bold', pad=20)\n",
    "plt.tight_layout()  # 自动调整布局，防止标签被截断\n",
    "\n",
    "# 10. 保存热力图（高分辨率）\n",
    "plt.savefig(\n",
    "    '17_热力图_变量相关性.png',\n",
    "    dpi=300,\n",
    "    bbox_inches='tight',\n",
    "    facecolor='white'\n",
    ")\n",
    "plt.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "13522c2e-4cb7-41f7-9da0-014f2ea89a13",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 1. 导入必需库\n",
    "import matplotlib.pyplot as plt\n",
    "import pandas as pd\n",
    "\n",
    "# 2. 设置中文字体（避免中文乱码）\n",
    "plt.rcParams['font.sans-serif'] = ['Microsoft YaHei', 'SimHei', 'Arial Unicode MS']\n",
    "plt.rcParams['axes.unicode_minus'] = False\n",
    "\n",
    "# 3. 读取所有需要的数据表（确保数据完整）\n",
    "user_df = pd.read_excel('simulated_data.xlsx', sheet_name='用户表')\n",
    "consume_df = pd.read_excel('simulated_data.xlsx', sheet_name='消费表')\n",
    "\n",
    "# 4. 合并数据生成user_consume（包含consume_category、user_id、consume_amount等字段）\n",
    "user_consume = pd.merge(\n",
    "    left=consume_df,\n",
    "    right=user_df[['user_id']],  # 只需user_id关联，其他字段暂不需要\n",
    "    on='user_id',\n",
    "    how='left'\n",
    ")\n",
    "\n",
    "# 5. 计算各品类关键指标（平均金额、消费用户数、订单数）\n",
    "bubble_data = user_consume.groupby('consume_category').agg({\n",
    "    'consume_amount': 'mean',    # 品类平均消费金额\n",
    "    'user_id': 'nunique',       # 品类消费用户数（去重）\n",
    "    'consume_id': 'count'       # 品类订单数（气泡大小依据）\n",
    "}).reset_index()\n",
    "\n",
    "# 6. 重命名列名（便于后续使用）\n",
    "bubble_data.columns = ['category', 'avg_amount', 'user_count', 'order_count']\n",
    "\n",
    "# 7. 数据预处理：调整气泡大小范围（避免气泡过大或过小）\n",
    "# 订单数可能差异大，用对数缩放或固定比例调整，确保显示效果\n",
    "bubble_data['order_count_scaled'] = bubble_data['order_count'] * 5  # 放大5倍，可根据实际调整\n",
    "\n",
    "# 8. 绘制气泡图（修正缩进和语法错误）\n",
    "plt.figure(figsize=(12, 8))\n",
    "\n",
    "# 核心：scatter函数参数统一缩进，修正多余括号\n",
    "scatter = plt.scatter(\n",
    "    x='user_count',          # x轴：消费用户数\n",
    "    y='avg_amount',          # y轴：平均消费金额\n",
    "    s='order_count_scaled',  # 气泡大小：缩放后的订单数\n",
    "    data=bubble_data,\n",
    "    alpha=0.7,               # 气泡透明度（避免重叠遮挡）\n",
    "    c=range(len(bubble_data)),  # 气泡颜色：按品类索引渐变\n",
    "    cmap='viridis'           # 配色方案（viridis：兼顾色盲友好）\n",
    ")\n",
    "\n",
    "# 9. 为每个气泡添加品类标签（避免混淆）\n",
    "for i, row in bubble_data.iterrows():\n",
    "    plt.annotate(\n",
    "        text=row['category'],  # 标签内容：品类名称\n",
    "        xy=(row['user_count'], row['avg_amount']),  # 标签位置：气泡中心点\n",
    "        xytext=(5, 5),  # 标签偏移量（右移5，上移5，避免覆盖气泡）\n",
    "        textcoords='offset points',  # 偏移量基于点的坐标\n",
    "        fontsize=10,    # 标签字体大小\n",
    "        bbox=dict(boxstyle='round,pad=0.3', facecolor='white', alpha=0.7)  # 标签背景（提升可读性）\n",
    "    )\n",
    "\n",
    "# 10. 图表美化\n",
    "plt.colorbar(scatter, label='品类索引')  # 颜色条：说明颜色对应的品类索引\n",
    "plt.title('消费品类-平均金额-用户数气泡图', fontsize=14, fontweight='bold', pad=20)\n",
    "plt.xlabel('消费用户数（人）', fontsize=12, fontweight='500')\n",
    "plt.ylabel('平均消费金额（元）', fontsize=12, fontweight='500')\n",
    "plt.grid(True, alpha=0.3, linestyle='-', linewidth=0.5)  # 浅色网格线（辅助读数）\n",
    "plt.tight_layout()  # 自动调整布局，防止标签被截断\n",
    "\n",
    "# 11. 保存气泡图（高分辨率）\n",
    "plt.savefig(\n",
    "    '18_气泡图_品类金额用户数.png',\n",
    "    dpi=300,\n",
    "    bbox_inches='tight',\n",
    "    facecolor='white'\n",
    ")\n",
    "plt.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "7cdeceb6-2bb6-40e5-82fc-94976554e875",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "数据验证通过，包含字段： ['consume_id', 'user_id', 'consume_amount', 'consume_category', 'payment_method', 'consume_time', 'age']\n"
     ]
    }
   ],
   "source": [
    "# 1. 导入必需库（含seaborn，用于散点图和直方图）\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import pandas as pd\n",
    "\n",
    "# 2. 关键：设置中文字体（避免中文乱码）\n",
    "plt.rcParams['font.sans-serif'] = ['Microsoft YaHei', 'SimHei', 'Arial Unicode MS']\n",
    "plt.rcParams['axes.unicode_minus'] = False\n",
    "\n",
    "# 3. 读取所需数据表（用户表含age，消费表含consume_amount）\n",
    "user_df = pd.read_excel('simulated_data.xlsx', sheet_name='用户表')  # 含age字段\n",
    "consume_df = pd.read_excel('simulated_data.xlsx', sheet_name='消费表')  # 含consume_amount字段\n",
    "\n",
    "# 4. 合并数据：关键是包含age字段（之前缺失的核心步骤）\n",
    "user_consume = pd.merge(\n",
    "    left=consume_df,  # 左表：消费表（含consume_amount）\n",
    "    right=user_df[['user_id', 'age']],  # 右表：用户表（必须包含age，用于x轴）\n",
    "    on='user_id',  # 通过user_id关联\n",
    "    how='left'  # 左连接：保留所有消费记录\n",
    ")\n",
    "\n",
    "# 5. 验证关键字段是否存在（提前排查问题）\n",
    "required_fields = ['age', 'consume_amount']\n",
    "if not all(field in user_consume.columns for field in required_fields):\n",
    "    raise ValueError(f\"user_consume缺失关键字段！需包含：{required_fields}\")\n",
    "print(\"数据验证通过，包含字段：\", user_consume.columns.tolist())\n",
    "\n",
    "# 6. 绘制边际分布图（修复后可正常运行）\n",
    "plt.figure(figsize=(12, 10))\n",
    "\n",
    "# 6.1 创建子图布局：3x3网格，主图占中间位置，边际图占上下/左右\n",
    "gs = plt.GridSpec(\n",
    "    3, 3, \n",
    "    height_ratios=[1, 3, 1],  # 垂直方向比例：顶部边际图1份，主图3份，底部留白1份\n",
    "    width_ratios=[1, 3, 1]    # 水平方向比例：左侧留白1份，主图3份，右侧边际图1份\n",
    ")\n",
    "main_ax = plt.subplot(gs[1, 1])  # 主图：第2行（索引1）第2列（索引1）\n",
    "top_ax = plt.subplot(gs[0, 1], sharex=main_ax)  # 顶部边际图：第1行第2列，共享x轴\n",
    "right_ax = plt.subplot(gs[1, 2], sharey=main_ax)  # 右侧边际图：第2行第3列，共享y轴\n",
    "\n",
    "# 6.2 主图：年龄-消费金额散点图（核心关联分析）\n",
    "sns.scatterplot(\n",
    "    x='age',  # 现在user_consume含age字段，可正常使用\n",
    "    y='consume_amount', \n",
    "    data=user_consume,\n",
    "    ax=main_ax,\n",
    "    alpha=0.6,  # 散点透明度（避免重叠遮挡）\n",
    "    color='#3498db',  # 散点颜色（蓝色）\n",
    "    s=60  # 散点大小（适中，避免过大）\n",
    ")\n",
    "main_ax.set_xlabel('年龄', fontsize=12, fontweight='500')\n",
    "main_ax.set_ylabel('消费金额（元）', fontsize=12, fontweight='500')\n",
    "main_ax.grid(True, alpha=0.3, linestyle='--')  # 浅色虚线网格（辅助读数）\n",
    "\n",
    "# 6.3 顶部边际图：年龄分布直方图（展示年龄整体分布）\n",
    "sns.histplot(\n",
    "    user_consume['age'],\n",
    "    ax=top_ax,\n",
    "    bins=12,  # 分12组，更细致展示年龄分布\n",
    "    kde=True,  # 显示核密度曲线（平滑分布趋势）\n",
    "    color='#e74c3c',  # 直方图颜色（红色）\n",
    "    edgecolor='black',  # 柱子边框（黑色，区分相邻柱子）\n",
    "    linewidth=0.5\n",
    ")\n",
    "top_ax.set_ylabel('频次', fontsize=10, fontweight='500')  # 只保留y轴标签（频次）\n",
    "top_ax.set_xlabel('')  # 隐藏x轴标签（与主图共享，避免重复）\n",
    "top_ax.grid(axis='y', alpha=0.3)  # 只显示y轴网格（更简洁）\n",
    "\n",
    "# 6.4 右侧边际图：消费金额分布直方图（水平方向，展示金额整体分布）\n",
    "sns.histplot(\n",
    "    user_consume['consume_amount'],\n",
    "    ax=right_ax,\n",
    "    bins=12,\n",
    "    kde=True,\n",
    "    color='#2ecc71',  # 直方图颜色（绿色）\n",
    "    edgecolor='black',\n",
    "    linewidth=0.5,\n",
    "    orientation='horizontal'  # 水平方向（适配右侧边际图布局）\n",
    ")\n",
    "right_ax.set_xlabel('频次', fontsize=10, fontweight='500')  # 只保留x轴标签（频次）\n",
    "right_ax.set_ylabel('')  # 隐藏y轴标签（与主图共享，避免重复）\n",
    "right_ax.grid(axis='x', alpha=0.3)  # 只显示x轴网格（更简洁）\n",
    "\n",
    "# 6.5 隐藏重复的坐标轴刻度标签（避免视觉冗余）\n",
    "plt.setp(top_ax.get_xticklabels(), visible=False)  # 隐藏顶部边际图的x轴刻度\n",
    "plt.setp(right_ax.get_yticklabels(), visible=False)  # 隐藏右侧边际图的y轴刻度\n",
    "\n",
    "# 6.6 整体标题和布局调整\n",
    "plt.suptitle(\n",
    "    '用户年龄-消费金额边际分布图', \n",
    "    fontsize=14, \n",
    "    fontweight='bold', \n",
    "    y=0.98  # 标题位置（略高于图表，避免遮挡）\n",
    ")\n",
    "plt.tight_layout()  # 自动调整子图间距\n",
    "plt.subplots_adjust(top=0.92)  # 预留顶部空间给总标题\n",
    "\n",
    "# 7. 保存图表（高分辨率，适合报告使用）\n",
    "plt.savefig(\n",
    "    '19_边际分布图_年龄消费.png',\n",
    "    dpi=300,\n",
    "    bbox_inches='tight',  # 紧凑布局，避免边缘被裁剪\n",
    "    facecolor='white'     # 背景色为白色（避免透明背景）\n",
    ")\n",
    "plt.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "49cd6e26-62b1-45e3-89fd-596ce17c654a",
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.figure(figsize=(12, 7))\n",
    "sns.boxplot(x='category', y='price', hue='department', data=product_df, palette='Set2')\n",
    "plt.title('不同部门各品类产品价格分布箱线图', fontsize=14, fontweight='bold')\n",
    "plt.xlabel('产品品类', fontsize=12)\n",
    "plt.ylabel('产品价格（元）', fontsize=12)\n",
    "plt.xticks(rotation=45)\n",
    "plt.grid(axis='y', alpha=0.3)\n",
    "plt.legend(title='部门')\n",
    "plt.tight_layout()\n",
    "plt.savefig('20_分组箱线图_部门品类价格.png', dpi=300)\n",
    "plt.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "3be5fd63-49bc-48bf-bca2-f92b64bb08ad",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "月度品类消费金额汇总表（前5行）：\n",
      "consume_category      家居用品      服装鞋帽      电子产品     美妆护肤     运动户外      食品生鲜\n",
      "consume_month                                                             \n",
      "2024-11            1718.16   1192.43   2212.34  1278.38  1232.60   1529.49\n",
      "2024-12           10825.11  14668.23   8480.16  7428.59  3298.04  10040.17\n",
      "2025-01            8660.64  14319.65  10434.46  3608.46  4610.72  13869.96\n",
      "2025-02           10086.08   9724.27   5701.13  4950.70  4238.62   8601.24\n",
      "2025-03           11470.92  15896.70  12753.50  4468.58  2989.97   7275.80\n",
      "\n",
      "数据维度：(13, 6)（13个月，6个品类）\n"
     ]
    },
    {
     "data": {
      "application/vnd.plotly.v1+json": {
       "config": {
        "plotlyServerURL": "https://plot.ly"
       },
       "data": [
        {
         "hovertemplate": "消费品类=家居用品<br>月份=%{x}<br>消费金额（元）=%{y:,.0f}<extra></extra>",
         "legendgroup": "家居用品",
         "line": {
          "color": "#636efa",
          "dash": "solid"
         },
         "marker": {
          "symbol": "circle"
         },
         "mode": "lines",
         "name": "家居用品",
         "orientation": "v",
         "showlegend": true,
         "type": "scatter",
         "x": [
          "2024-11",
          "2024-12",
          "2025-01",
          "2025-02",
          "2025-03",
          "2025-04",
          "2025-05",
          "2025-06",
          "2025-07",
          "2025-08",
          "2025-09",
          "2025-10",
          "2025-11"
         ],
         "xaxis": "x",
         "y": {
          "bdata": "cT0K16PYmkBI4XoUjiTFQLgehetR6sBA16NwPQqzw0ApXI/CdWfGQOxRuB7FI7xA9ihcj4IRvkAehetRuIy+QFyPwvVo9bVAzczMzGzVwkD2KFyPQlC1QPYoXI8CWbJA16NwPYotuUA=",
          "dtype": "f8"
         },
         "yaxis": "y"
        },
        {
         "hovertemplate": "消费品类=服装鞋帽<br>月份=%{x}<br>消费金额（元）=%{y:,.0f}<extra></extra>",
         "legendgroup": "服装鞋帽",
         "line": {
          "color": "#EF553B",
          "dash": "solid"
         },
         "marker": {
          "symbol": "circle"
         },
         "mode": "lines",
         "name": "服装鞋帽",
         "orientation": "v",
         "showlegend": true,
         "type": "scatter",
         "x": [
          "2024-11",
          "2024-12",
          "2025-01",
          "2025-02",
          "2025-03",
          "2025-04",
          "2025-05",
          "2025-06",
          "2025-07",
          "2025-08",
          "2025-09",
          "2025-10",
          "2025-11"
         ],
         "xaxis": "x",
         "y": {
          "bdata": "HoXrUbihkkAK16NwHabMQDMzMzPT98tA9ihcjyL+wkCamZmZWQzPQBWuR+H66MdAAAAAACBE0kBxPQrXA1zOQFyPwvUoecRAcT0K1wN/yEBcj8L1CLjNQPYoXI/CjcJAmpmZmfnywEA=",
          "dtype": "f8"
         },
         "yaxis": "y"
        },
        {
         "hovertemplate": "消费品类=电子产品<br>月份=%{x}<br>消费金额（元）=%{y:,.0f}<extra></extra>",
         "legendgroup": "电子产品",
         "line": {
          "color": "#00cc96",
          "dash": "solid"
         },
         "marker": {
          "symbol": "circle"
         },
         "mode": "lines",
         "name": "电子产品",
         "orientation": "v",
         "showlegend": true,
         "type": "scatter",
         "x": [
          "2024-11",
          "2024-12",
          "2025-01",
          "2025-02",
          "2025-03",
          "2025-04",
          "2025-05",
          "2025-06",
          "2025-07",
          "2025-08",
          "2025-09",
          "2025-10",
          "2025-11"
         ],
         "xaxis": "x",
         "y": {
          "bdata": "SOF6FK5IoUCuR+F6FJDAQBSuR+E6YcRAexSuRyFFtkAAAAAAwOjIQD4K16OwcMhAcT0K16NVwkDhehSuJ7vDQClcj8L1nsJAcT0K1wNDxEAzMzMz88K5QClcj8J1i7xAexSuR2GeuUA=",
          "dtype": "f8"
         },
         "yaxis": "y"
        },
        {
         "hovertemplate": "消费品类=美妆护肤<br>月份=%{x}<br>消费金额（元）=%{y:,.0f}<extra></extra>",
         "legendgroup": "美妆护肤",
         "line": {
          "color": "#ab63fa",
          "dash": "solid"
         },
         "marker": {
          "symbol": "circle"
         },
         "mode": "lines",
         "name": "美妆护肤",
         "orientation": "v",
         "showlegend": true,
         "type": "scatter",
         "x": [
          "2024-11",
          "2024-12",
          "2025-01",
          "2025-02",
          "2025-03",
          "2025-04",
          "2025-05",
          "2025-06",
          "2025-07",
          "2025-08",
          "2025-09",
          "2025-10",
          "2025-11"
         ],
         "xaxis": "x",
         "y": {
          "bdata": "7FG4HoX5k0CkcD0KlwS9QFK4HoXrMKxAMzMzM7NWs0CuR+F6lHSxQLgehevR/KlACtejcH13skB7FK5HoYi5QDMzMzMzU79AXI/C9aiatkD2KFyPwhKtQGZmZmamd7RAj8L1KByPskA=",
          "dtype": "f8"
         },
         "yaxis": "y"
        },
        {
         "hovertemplate": "消费品类=运动户外<br>月份=%{x}<br>消费金额（元）=%{y:,.0f}<extra></extra>",
         "legendgroup": "运动户外",
         "line": {
          "color": "#FFA15A",
          "dash": "solid"
         },
         "marker": {
          "symbol": "circle"
         },
         "mode": "lines",
         "name": "运动户外",
         "orientation": "v",
         "showlegend": true,
         "type": "scatter",
         "x": [
          "2024-11",
          "2024-12",
          "2025-01",
          "2025-02",
          "2025-03",
          "2025-04",
          "2025-05",
          "2025-06",
          "2025-07",
          "2025-08",
          "2025-09",
          "2025-10",
          "2025-11"
         ],
         "xaxis": "x",
         "y": {
          "bdata": "ZmZmZmZCk0CuR+F6FMSpQB+F61G4ArJAhetRuJ6OsEA9Ctej8FunQIXrUbjecLVAwvUoXA8nuEDsUbgeBXK3QFyPwvUo0q1AwvUoXA80tEBI4XoU7t+yQGZmZmYmuLFAPgrXo7BhskA=",
          "dtype": "f8"
         },
         "yaxis": "y"
        },
        {
         "hovertemplate": "消费品类=食品生鲜<br>月份=%{x}<br>消费金额（元）=%{y:,.0f}<extra></extra>",
         "legendgroup": "食品生鲜",
         "line": {
          "color": "#19d3f3",
          "dash": "solid"
         },
         "marker": {
          "symbol": "circle"
         },
         "mode": "lines",
         "name": "食品生鲜",
         "orientation": "v",
         "showlegend": true,
         "type": "scatter",
         "x": [
          "2024-11",
          "2024-12",
          "2025-01",
          "2025-02",
          "2025-03",
          "2025-04",
          "2025-05",
          "2025-06",
          "2025-07",
          "2025-08",
          "2025-09",
          "2025-10",
          "2025-11"
         ],
         "xaxis": "x",
         "y": {
          "bdata": "KFyPwvXll0ApXI/CFZzDQBWuR+H6FstAhetRuJ7MwEDNzMzMzGu8QAAAAABAOcJAAAAAAOCDxECuR+F6NJ3HQIXrUbj+T8FAAAAAAMC0ykCamZmZ2QW9QLgehevxLMtAcD0K1yPVu0A=",
          "dtype": "f8"
         },
         "yaxis": "y"
        }
       ],
       "layout": {
        "height": 600,
        "hovermode": "x unified",
        "legend": {
         "orientation": "h",
         "title": {
          "text": "消费品类"
         },
         "tracegroupgap": 0,
         "x": 1,
         "xanchor": "right",
         "y": 1.02,
         "yanchor": "bottom"
        },
        "template": {
         "data": {
          "bar": [
           {
            "error_x": {
             "color": "#2a3f5f"
            },
            "error_y": {
             "color": "#2a3f5f"
            },
            "marker": {
             "line": {
              "color": "white",
              "width": 0.5
             },
             "pattern": {
              "fillmode": "overlay",
              "size": 10,
              "solidity": 0.2
             }
            },
            "type": "bar"
           }
          ],
          "barpolar": [
           {
            "marker": {
             "line": {
              "color": "white",
              "width": 0.5
             },
             "pattern": {
              "fillmode": "overlay",
              "size": 10,
              "solidity": 0.2
             }
            },
            "type": "barpolar"
           }
          ],
          "carpet": [
           {
            "aaxis": {
             "endlinecolor": "#2a3f5f",
             "gridcolor": "#C8D4E3",
             "linecolor": "#C8D4E3",
             "minorgridcolor": "#C8D4E3",
             "startlinecolor": "#2a3f5f"
            },
            "baxis": {
             "endlinecolor": "#2a3f5f",
             "gridcolor": "#C8D4E3",
             "linecolor": "#C8D4E3",
             "minorgridcolor": "#C8D4E3",
             "startlinecolor": "#2a3f5f"
            },
            "type": "carpet"
           }
          ],
          "choropleth": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "type": "choropleth"
           }
          ],
          "contour": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "contour"
           }
          ],
          "contourcarpet": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "type": "contourcarpet"
           }
          ],
          "heatmap": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "heatmap"
           }
          ],
          "histogram": [
           {
            "marker": {
             "pattern": {
              "fillmode": "overlay",
              "size": 10,
              "solidity": 0.2
             }
            },
            "type": "histogram"
           }
          ],
          "histogram2d": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "histogram2d"
           }
          ],
          "histogram2dcontour": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "histogram2dcontour"
           }
          ],
          "mesh3d": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "type": "mesh3d"
           }
          ],
          "parcoords": [
           {
            "line": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "parcoords"
           }
          ],
          "pie": [
           {
            "automargin": true,
            "type": "pie"
           }
          ],
          "scatter": [
           {
            "fillpattern": {
             "fillmode": "overlay",
             "size": 10,
             "solidity": 0.2
            },
            "type": "scatter"
           }
          ],
          "scatter3d": [
           {
            "line": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatter3d"
           }
          ],
          "scattercarpet": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattercarpet"
           }
          ],
          "scattergeo": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattergeo"
           }
          ],
          "scattergl": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattergl"
           }
          ],
          "scattermap": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattermap"
           }
          ],
          "scattermapbox": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattermapbox"
           }
          ],
          "scatterpolar": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatterpolar"
           }
          ],
          "scatterpolargl": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatterpolargl"
           }
          ],
          "scatterternary": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatterternary"
           }
          ],
          "surface": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "surface"
           }
          ],
          "table": [
           {
            "cells": {
             "fill": {
              "color": "#EBF0F8"
             },
             "line": {
              "color": "white"
             }
            },
            "header": {
             "fill": {
              "color": "#C8D4E3"
             },
             "line": {
              "color": "white"
             }
            },
            "type": "table"
           }
          ]
         },
         "layout": {
          "annotationdefaults": {
           "arrowcolor": "#2a3f5f",
           "arrowhead": 0,
           "arrowwidth": 1
          },
          "autotypenumbers": "strict",
          "coloraxis": {
           "colorbar": {
            "outlinewidth": 0,
            "ticks": ""
           }
          },
          "colorscale": {
           "diverging": [
            [
             0,
             "#8e0152"
            ],
            [
             0.1,
             "#c51b7d"
            ],
            [
             0.2,
             "#de77ae"
            ],
            [
             0.3,
             "#f1b6da"
            ],
            [
             0.4,
             "#fde0ef"
            ],
            [
             0.5,
             "#f7f7f7"
            ],
            [
             0.6,
             "#e6f5d0"
            ],
            [
             0.7,
             "#b8e186"
            ],
            [
             0.8,
             "#7fbc41"
            ],
            [
             0.9,
             "#4d9221"
            ],
            [
             1,
             "#276419"
            ]
           ],
           "sequential": [
            [
             0,
             "#0d0887"
            ],
            [
             0.1111111111111111,
             "#46039f"
            ],
            [
             0.2222222222222222,
             "#7201a8"
            ],
            [
             0.3333333333333333,
             "#9c179e"
            ],
            [
             0.4444444444444444,
             "#bd3786"
            ],
            [
             0.5555555555555556,
             "#d8576b"
            ],
            [
             0.6666666666666666,
             "#ed7953"
            ],
            [
             0.7777777777777778,
             "#fb9f3a"
            ],
            [
             0.8888888888888888,
             "#fdca26"
            ],
            [
             1,
             "#f0f921"
            ]
           ],
           "sequentialminus": [
            [
             0,
             "#0d0887"
            ],
            [
             0.1111111111111111,
             "#46039f"
            ],
            [
             0.2222222222222222,
             "#7201a8"
            ],
            [
             0.3333333333333333,
             "#9c179e"
            ],
            [
             0.4444444444444444,
             "#bd3786"
            ],
            [
             0.5555555555555556,
             "#d8576b"
            ],
            [
             0.6666666666666666,
             "#ed7953"
            ],
            [
             0.7777777777777778,
             "#fb9f3a"
            ],
            [
             0.8888888888888888,
             "#fdca26"
            ],
            [
             1,
             "#f0f921"
            ]
           ]
          },
          "colorway": [
           "#636efa",
           "#EF553B",
           "#00cc96",
           "#ab63fa",
           "#FFA15A",
           "#19d3f3",
           "#FF6692",
           "#B6E880",
           "#FF97FF",
           "#FECB52"
          ],
          "font": {
           "color": "#2a3f5f"
          },
          "geo": {
           "bgcolor": "white",
           "lakecolor": "white",
           "landcolor": "white",
           "showlakes": true,
           "showland": true,
           "subunitcolor": "#C8D4E3"
          },
          "hoverlabel": {
           "align": "left"
          },
          "hovermode": "closest",
          "mapbox": {
           "style": "light"
          },
          "paper_bgcolor": "white",
          "plot_bgcolor": "white",
          "polar": {
           "angularaxis": {
            "gridcolor": "#EBF0F8",
            "linecolor": "#EBF0F8",
            "ticks": ""
           },
           "bgcolor": "white",
           "radialaxis": {
            "gridcolor": "#EBF0F8",
            "linecolor": "#EBF0F8",
            "ticks": ""
           }
          },
          "scene": {
           "xaxis": {
            "backgroundcolor": "white",
            "gridcolor": "#DFE8F3",
            "gridwidth": 2,
            "linecolor": "#EBF0F8",
            "showbackground": true,
            "ticks": "",
            "zerolinecolor": "#EBF0F8"
           },
           "yaxis": {
            "backgroundcolor": "white",
            "gridcolor": "#DFE8F3",
            "gridwidth": 2,
            "linecolor": "#EBF0F8",
            "showbackground": true,
            "ticks": "",
            "zerolinecolor": "#EBF0F8"
           },
           "zaxis": {
            "backgroundcolor": "white",
            "gridcolor": "#DFE8F3",
            "gridwidth": 2,
            "linecolor": "#EBF0F8",
            "showbackground": true,
            "ticks": "",
            "zerolinecolor": "#EBF0F8"
           }
          },
          "shapedefaults": {
           "line": {
            "color": "#2a3f5f"
           }
          },
          "ternary": {
           "aaxis": {
            "gridcolor": "#DFE8F3",
            "linecolor": "#A2B1C6",
            "ticks": ""
           },
           "baxis": {
            "gridcolor": "#DFE8F3",
            "linecolor": "#A2B1C6",
            "ticks": ""
           },
           "bgcolor": "white",
           "caxis": {
            "gridcolor": "#DFE8F3",
            "linecolor": "#A2B1C6",
            "ticks": ""
           }
          },
          "title": {
           "x": 0.05
          },
          "xaxis": {
           "automargin": true,
           "gridcolor": "#EBF0F8",
           "linecolor": "#EBF0F8",
           "ticks": "",
           "title": {
            "standoff": 15
           },
           "zerolinecolor": "#EBF0F8",
           "zerolinewidth": 2
          },
          "yaxis": {
           "automargin": true,
           "gridcolor": "#EBF0F8",
           "linecolor": "#EBF0F8",
           "ticks": "",
           "title": {
            "standoff": 15
           },
           "zerolinecolor": "#EBF0F8",
           "zerolinewidth": 2
          }
         }
        },
        "title": {
         "text": "月度各品类消费金额趋势（交互式）"
        },
        "width": 1200,
        "xaxis": {
         "anchor": "y",
         "domain": [
          0,
          1
         ],
         "tickangle": -45,
         "title": {
          "text": "月份"
         }
        },
        "yaxis": {
         "anchor": "x",
         "domain": [
          0,
          1
         ],
         "title": {
          "text": "消费金额（元）"
         }
        }
       }
      }
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# 1. 导入必需库（plotly.express用于交互式图表，pandas用于数据处理）\n",
    "import plotly.express as px\n",
    "import pandas as pd\n",
    "\n",
    "# 2. 读取基础数据（消费表+用户表，确保包含消费时间和品类信息）\n",
    "consume_df = pd.read_excel('simulated_data.xlsx', sheet_name='消费表')\n",
    "user_df = pd.read_excel('simulated_data.xlsx', sheet_name='用户表')  # 若需补充字段可关联，此处基础分析暂用消费表\n",
    "\n",
    "# 3. 关键步骤1：数据预处理 - 转换消费时间为“年月”格式，便于月度分组\n",
    "# 将consume_time转换为datetime类型（确保格式统一）\n",
    "consume_df['consume_time'] = pd.to_datetime(consume_df['consume_time'])\n",
    "# 新增“年月”字段（格式：YYYY-MM），作为分组依据\n",
    "consume_df['consume_month'] = consume_df['consume_time'].dt.to_period('M').astype(str)\n",
    "\n",
    "# 4. 关键步骤2：创建monthly_category - 按“年月+品类”汇总消费金额\n",
    "monthly_category = consume_df.groupby(['consume_month', 'consume_category'])['consume_amount'].sum().unstack(fill_value=0)\n",
    "# 说明：unstack()将“品类”从行转换为列，形成“月度-品类”的宽格式数据，便于后续处理\n",
    "\n",
    "# 5. 验证monthly_category是否创建成功\n",
    "print(\"月度品类消费金额汇总表（前5行）：\")\n",
    "print(monthly_category.head())\n",
    "print(f\"\\n数据维度：{monthly_category.shape}（{monthly_category.index.nunique()}个月，{monthly_category.columns.nunique()}个品类）\")\n",
    "\n",
    "# 6. 转换为长格式（plotly.express更适合长格式数据）\n",
    "monthly_category_long = monthly_category.reset_index()  # 将“年月”从索引转为列\n",
    "monthly_category_long = monthly_category_long.melt(\n",
    "    id_vars=['consume_month'],  # 保留的标识列：年月\n",
    "    var_name='category',        # 转换后的品类列名\n",
    "    value_name='amount'         # 转换后的消费金额列名\n",
    ")\n",
    "\n",
    "# 7. 绘制交互式折线图（修复后可正常运行）\n",
    "fig = px.line(\n",
    "    monthly_category_long,\n",
    "    x='consume_month',          # x轴：月份（YYYY-MM）\n",
    "    y='amount',                 # y轴：消费金额\n",
    "    color='category',           # 按品类区分线条颜色\n",
    "    title='月度各品类消费金额趋势（交互式）',\n",
    "    labels={                    # 自定义坐标轴和图例标签（中文友好）\n",
    "        'consume_month': '月份',\n",
    "        'amount': '消费金额（元）',\n",
    "        'category': '消费品类'\n",
    "    },\n",
    "    hover_data={'amount': ':,.0f'},  # hover时显示格式化金额（千分位分隔）\n",
    "    template='plotly_white'     # 白色模板（更简洁，适合汇报）\n",
    ")\n",
    "\n",
    "# 8. 美化交互式图表布局（提升用户体验）\n",
    "fig.update_layout(\n",
    "    xaxis_tickangle=-45,        # x轴标签旋转45度，避免重叠\n",
    "    xaxis_title='月份',          # x轴标题\n",
    "    yaxis_title='消费金额（元）', # y轴标题\n",
    "    legend_title='消费品类',     # 图例标题\n",
    "    hovermode='x unified',      # hover时显示同一x值（同一月份）的所有品类数据\n",
    "    legend=dict(\n",
    "        orientation='h',        # 图例水平排列（节省垂直空间）\n",
    "        yanchor='bottom',       # 图例锚点在底部\n",
    "        y=1.02,                 # 图例位置（在图表上方）\n",
    "        xanchor='right',        # 图例x锚点在右侧\n",
    "        x=1                     # 图例x坐标（右对齐）\n",
    "    ),\n",
    "    width=1200,                 # 图表宽度\n",
    "    height=600                  # 图表高度\n",
    ")\n",
    "\n",
    "# 9. 保存为HTML文件（可双击打开，支持交互操作）\n",
    "fig.write_html('21_交互式折线图_月度品类趋势.html')\n",
    "\n",
    "# 10. 在Notebook中预览（若使用Notebook环境）\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "2bd3f948-5a0d-42e8-bfeb-6ff9e9f0b539",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.plotly.v1+json": {
       "config": {
        "plotlyServerURL": "https://plot.ly"
       },
       "data": [
        {
         "hovertemplate": "城市=%{x}<br>总消费金额（元）=%{marker.color}<extra></extra>",
         "legendgroup": "",
         "marker": {
          "color": {
           "bdata": "AAAAAIATykD2KFyPwprHQHsUrkdhbMdAZmZmZoYfxUDXo3A9KgnFQB+F61GYcsNAKVyPwrXmwkDsUbgeBYTCQOF6FK4HccJAj8L1KDyswUA=",
           "dtype": "f8"
          },
          "coloraxis": "coloraxis",
          "pattern": {
           "shape": ""
          }
         },
         "name": "",
         "orientation": "v",
         "showlegend": false,
         "text": {
          "bdata": "AAAAAIATykD2KFyPwprHQHsUrkdhbMdAZmZmZoYfxUDXo3A9KgnFQB+F61GYcsNAKVyPwrXmwkDsUbgeBYTCQOF6FK4HccJAj8L1KDyswUA=",
          "dtype": "f8"
         },
         "textposition": "outside",
         "texttemplate": "%{text:,.0f}",
         "type": "bar",
         "x": [
          "欣市",
          "惠州市",
          "海口市",
          "璐县",
          "太原县",
          "丽丽市",
          "六盘水市",
          "潜江市",
          "浩市",
          "建军市"
         ],
         "xaxis": "x",
         "y": {
          "bdata": "AAAAAIATykD2KFyPwprHQHsUrkdhbMdAZmZmZoYfxUDXo3A9KgnFQB+F61GYcsNAKVyPwrXmwkDsUbgeBYTCQOF6FK4HccJAj8L1KDyswUA=",
          "dtype": "f8"
         },
         "yaxis": "y"
        }
       ],
       "layout": {
        "barmode": "relative",
        "coloraxis": {
         "colorbar": {
          "title": {
           "text": "总金额"
          }
         },
         "colorscale": [
          [
           0,
           "rgb(49,54,149)"
          ],
          [
           0.1,
           "rgb(69,117,180)"
          ],
          [
           0.2,
           "rgb(116,173,209)"
          ],
          [
           0.3,
           "rgb(171,217,233)"
          ],
          [
           0.4,
           "rgb(224,243,248)"
          ],
          [
           0.5,
           "rgb(255,255,191)"
          ],
          [
           0.6,
           "rgb(254,224,144)"
          ],
          [
           0.7,
           "rgb(253,174,97)"
          ],
          [
           0.8,
           "rgb(244,109,67)"
          ],
          [
           0.9,
           "rgb(215,48,39)"
          ],
          [
           1,
           "rgb(165,0,38)"
          ]
         ]
        },
        "height": 360,
        "legend": {
         "tracegroupgap": 0
        },
        "template": {
         "data": {
          "bar": [
           {
            "error_x": {
             "color": "#2a3f5f"
            },
            "error_y": {
             "color": "#2a3f5f"
            },
            "marker": {
             "line": {
              "color": "#E5ECF6",
              "width": 0.5
             },
             "pattern": {
              "fillmode": "overlay",
              "size": 10,
              "solidity": 0.2
             }
            },
            "type": "bar"
           }
          ],
          "barpolar": [
           {
            "marker": {
             "line": {
              "color": "#E5ECF6",
              "width": 0.5
             },
             "pattern": {
              "fillmode": "overlay",
              "size": 10,
              "solidity": 0.2
             }
            },
            "type": "barpolar"
           }
          ],
          "carpet": [
           {
            "aaxis": {
             "endlinecolor": "#2a3f5f",
             "gridcolor": "white",
             "linecolor": "white",
             "minorgridcolor": "white",
             "startlinecolor": "#2a3f5f"
            },
            "baxis": {
             "endlinecolor": "#2a3f5f",
             "gridcolor": "white",
             "linecolor": "white",
             "minorgridcolor": "white",
             "startlinecolor": "#2a3f5f"
            },
            "type": "carpet"
           }
          ],
          "choropleth": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "type": "choropleth"
           }
          ],
          "contour": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "contour"
           }
          ],
          "contourcarpet": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "type": "contourcarpet"
           }
          ],
          "heatmap": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "heatmap"
           }
          ],
          "histogram": [
           {
            "marker": {
             "pattern": {
              "fillmode": "overlay",
              "size": 10,
              "solidity": 0.2
             }
            },
            "type": "histogram"
           }
          ],
          "histogram2d": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "histogram2d"
           }
          ],
          "histogram2dcontour": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "histogram2dcontour"
           }
          ],
          "mesh3d": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "type": "mesh3d"
           }
          ],
          "parcoords": [
           {
            "line": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "parcoords"
           }
          ],
          "pie": [
           {
            "automargin": true,
            "type": "pie"
           }
          ],
          "scatter": [
           {
            "fillpattern": {
             "fillmode": "overlay",
             "size": 10,
             "solidity": 0.2
            },
            "type": "scatter"
           }
          ],
          "scatter3d": [
           {
            "line": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatter3d"
           }
          ],
          "scattercarpet": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattercarpet"
           }
          ],
          "scattergeo": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattergeo"
           }
          ],
          "scattergl": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattergl"
           }
          ],
          "scattermap": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattermap"
           }
          ],
          "scattermapbox": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scattermapbox"
           }
          ],
          "scatterpolar": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatterpolar"
           }
          ],
          "scatterpolargl": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatterpolargl"
           }
          ],
          "scatterternary": [
           {
            "marker": {
             "colorbar": {
              "outlinewidth": 0,
              "ticks": ""
             }
            },
            "type": "scatterternary"
           }
          ],
          "surface": [
           {
            "colorbar": {
             "outlinewidth": 0,
             "ticks": ""
            },
            "colorscale": [
             [
              0,
              "#0d0887"
             ],
             [
              0.1111111111111111,
              "#46039f"
             ],
             [
              0.2222222222222222,
              "#7201a8"
             ],
             [
              0.3333333333333333,
              "#9c179e"
             ],
             [
              0.4444444444444444,
              "#bd3786"
             ],
             [
              0.5555555555555556,
              "#d8576b"
             ],
             [
              0.6666666666666666,
              "#ed7953"
             ],
             [
              0.7777777777777778,
              "#fb9f3a"
             ],
             [
              0.8888888888888888,
              "#fdca26"
             ],
             [
              1,
              "#f0f921"
             ]
            ],
            "type": "surface"
           }
          ],
          "table": [
           {
            "cells": {
             "fill": {
              "color": "#EBF0F8"
             },
             "line": {
              "color": "white"
             }
            },
            "header": {
             "fill": {
              "color": "#C8D4E3"
             },
             "line": {
              "color": "white"
             }
            },
            "type": "table"
           }
          ]
         },
         "layout": {
          "annotationdefaults": {
           "arrowcolor": "#2a3f5f",
           "arrowhead": 0,
           "arrowwidth": 1
          },
          "autotypenumbers": "strict",
          "coloraxis": {
           "colorbar": {
            "outlinewidth": 0,
            "ticks": ""
           }
          },
          "colorscale": {
           "diverging": [
            [
             0,
             "#8e0152"
            ],
            [
             0.1,
             "#c51b7d"
            ],
            [
             0.2,
             "#de77ae"
            ],
            [
             0.3,
             "#f1b6da"
            ],
            [
             0.4,
             "#fde0ef"
            ],
            [
             0.5,
             "#f7f7f7"
            ],
            [
             0.6,
             "#e6f5d0"
            ],
            [
             0.7,
             "#b8e186"
            ],
            [
             0.8,
             "#7fbc41"
            ],
            [
             0.9,
             "#4d9221"
            ],
            [
             1,
             "#276419"
            ]
           ],
           "sequential": [
            [
             0,
             "#0d0887"
            ],
            [
             0.1111111111111111,
             "#46039f"
            ],
            [
             0.2222222222222222,
             "#7201a8"
            ],
            [
             0.3333333333333333,
             "#9c179e"
            ],
            [
             0.4444444444444444,
             "#bd3786"
            ],
            [
             0.5555555555555556,
             "#d8576b"
            ],
            [
             0.6666666666666666,
             "#ed7953"
            ],
            [
             0.7777777777777778,
             "#fb9f3a"
            ],
            [
             0.8888888888888888,
             "#fdca26"
            ],
            [
             1,
             "#f0f921"
            ]
           ],
           "sequentialminus": [
            [
             0,
             "#0d0887"
            ],
            [
             0.1111111111111111,
             "#46039f"
            ],
            [
             0.2222222222222222,
             "#7201a8"
            ],
            [
             0.3333333333333333,
             "#9c179e"
            ],
            [
             0.4444444444444444,
             "#bd3786"
            ],
            [
             0.5555555555555556,
             "#d8576b"
            ],
            [
             0.6666666666666666,
             "#ed7953"
            ],
            [
             0.7777777777777778,
             "#fb9f3a"
            ],
            [
             0.8888888888888888,
             "#fdca26"
            ],
            [
             1,
             "#f0f921"
            ]
           ]
          },
          "colorway": [
           "#636efa",
           "#EF553B",
           "#00cc96",
           "#ab63fa",
           "#FFA15A",
           "#19d3f3",
           "#FF6692",
           "#B6E880",
           "#FF97FF",
           "#FECB52"
          ],
          "font": {
           "color": "#2a3f5f"
          },
          "geo": {
           "bgcolor": "white",
           "lakecolor": "white",
           "landcolor": "#E5ECF6",
           "showlakes": true,
           "showland": true,
           "subunitcolor": "white"
          },
          "hoverlabel": {
           "align": "left"
          },
          "hovermode": "closest",
          "mapbox": {
           "style": "light"
          },
          "paper_bgcolor": "white",
          "plot_bgcolor": "#E5ECF6",
          "polar": {
           "angularaxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           },
           "bgcolor": "#E5ECF6",
           "radialaxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           }
          },
          "scene": {
           "xaxis": {
            "backgroundcolor": "#E5ECF6",
            "gridcolor": "white",
            "gridwidth": 2,
            "linecolor": "white",
            "showbackground": true,
            "ticks": "",
            "zerolinecolor": "white"
           },
           "yaxis": {
            "backgroundcolor": "#E5ECF6",
            "gridcolor": "white",
            "gridwidth": 2,
            "linecolor": "white",
            "showbackground": true,
            "ticks": "",
            "zerolinecolor": "white"
           },
           "zaxis": {
            "backgroundcolor": "#E5ECF6",
            "gridcolor": "white",
            "gridwidth": 2,
            "linecolor": "white",
            "showbackground": true,
            "ticks": "",
            "zerolinecolor": "white"
           }
          },
          "shapedefaults": {
           "line": {
            "color": "#2a3f5f"
           }
          },
          "ternary": {
           "aaxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           },
           "baxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           },
           "bgcolor": "#E5ECF6",
           "caxis": {
            "gridcolor": "white",
            "linecolor": "white",
            "ticks": ""
           }
          },
          "title": {
           "x": 0.05
          },
          "xaxis": {
           "automargin": true,
           "gridcolor": "white",
           "linecolor": "white",
           "ticks": "",
           "title": {
            "standoff": 15
           },
           "zerolinecolor": "white",
           "zerolinewidth": 2
          },
          "yaxis": {
           "automargin": true,
           "gridcolor": "white",
           "linecolor": "white",
           "ticks": "",
           "title": {
            "standoff": 15
           },
           "zerolinecolor": "white",
           "zerolinewidth": 2
          }
         }
        },
        "title": {
         "text": "各城市总消费金额TOP10（交互式）"
        },
        "xaxis": {
         "anchor": "y",
         "domain": [
          0,
          1
         ],
         "tickangle": -45,
         "title": {
          "text": "城市"
         }
        },
        "yaxis": {
         "anchor": "x",
         "domain": [
          0,
          1
         ],
         "title": {
          "text": "总消费金额（元）"
         }
        }
       }
      }
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# 计算各城市总消费金额TOP10\n",
    "city_total = user_consume.merge(user_df[['user_id', 'city']], on='user_id').groupby('city')['consume_amount'].sum().sort_values(ascending=False).head(10)\n",
    "city_df = city_total.reset_index()\n",
    "city_df.columns = ['city', 'total_amount']\n",
    "\n",
    "# 绘制交互式柱状图\n",
    "fig = px.bar(\n",
    "    city_df,\n",
    "    x='city',\n",
    "    y='total_amount',\n",
    "    color='total_amount',\n",
    "    color_continuous_scale='RdYlBu_r',\n",
    "    title='各城市总消费金额TOP10（交互式）',\n",
    "    labels={'city': '城市', 'total_amount': '总消费金额（元）'},\n",
    "    text='total_amount'\n",
    ")\n",
    "# 格式化数值标签\n",
    "fig.update_traces(\n",
    "    texttemplate='%{text:,.0f}',\n",
    "    textposition='outside'\n",
    ")\n",
    "# 美化布局\n",
    "fig.update_layout(\n",
    "    xaxis_tickangle=-45,\n",
    "    coloraxis_colorbar_title='总金额',\n",
    "    yaxis_title='总消费金额（元）'\n",
    ")\n",
    "fig.write_html('22_交互式柱状图_城市消费TOP10.html')\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "6c13962b-a05c-4d84-8c2a-3cd382c59464",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "各省份消费金额统计（前5个）：\n",
      "1. 上海市: 3,437 元\n",
      "2. 北京市: 4,417 元\n",
      "\n",
      "动态地图生成完成！文件已保存为：23_动态地图_省份消费分布.html\n",
      "交互功能说明：\n",
      "1. 鼠标hover任意省份，可查看该省份的具体消费金额\n",
      "2. 点击右侧分段标签，可隐藏/显示对应金额范围的省份\n",
      "3. 支持缩放、平移地图，查看细节区域\n"
     ]
    }
   ],
   "source": [
    "# 1. 导入所有必需库（pyecharts用于动态地图，pandas用于数据处理）\n",
    "from pyecharts import options as opts\n",
    "from pyecharts.charts import Map\n",
    "from pyecharts.globals import ThemeType\n",
    "import pandas as pd\n",
    "\n",
    "# 2. 关键步骤1：读取基础数据（必须先读取，才能使用user_df和consume_df）\n",
    "# 读取用户表（含user_id、city字段，用于提取省份）\n",
    "user_df = pd.read_excel('simulated_data.xlsx', sheet_name='用户表')\n",
    "# 读取消费表（含user_id、consume_amount字段，用于计算省份消费金额）\n",
    "consume_df = pd.read_excel('simulated_data.xlsx', sheet_name='消费表')\n",
    "\n",
    "# 3. 关键步骤2：合并数据生成user_consume（关联用户和消费记录）\n",
    "# 只有合并后，才能通过用户的city字段匹配到消费金额\n",
    "user_consume = pd.merge(\n",
    "    left=consume_df,                # 左表：消费记录（含消费金额）\n",
    "    right=user_df[['user_id', 'city']],  # 右表：用户信息（仅取需要的ID和城市）\n",
    "    on='user_id',                   # 关联字段：用户ID（确保数据一一对应）\n",
    "    how='left'                      # 左连接：保留所有消费记录，避免数据丢失\n",
    ")\n",
    "\n",
    "# 4. 提取省份（从城市名处理，确保与地图匹配）\n",
    "# 4.1 先截取城市名前2个字符（如“北京市”→“北京”，“上海市”→“上海”）\n",
    "user_df['province_short'] = user_df['city'].str[:2]\n",
    "\n",
    "# 4.2 映射为标准省份名称（解决“简称→全称”问题，确保pyecharts地图能识别）\n",
    "standard_provinces = {\n",
    "    '北京': '北京市', '上海': '上海市', '天津': '天津市', '重庆': '重庆市',\n",
    "    '河北': '河北省', '山西': '山西省', '辽宁': '辽宁省', '吉林': '吉林省', '黑龙江': '黑龙江省',\n",
    "    '江苏': '江苏省', '浙江': '浙江省', '安徽': '安徽省', '福建': '福建省', '江西': '江西省', '山东': '山东省',\n",
    "    '河南': '河南省', '湖北': '湖北省', '湖南': '湖南省', '广东': '广东省', '海南': '海南省',\n",
    "    '四川': '四川省', '贵州': '贵州省', '云南': '云南省', '陕西': '陕西省', '甘肃': '甘肃省', '青海': '青海省',\n",
    "    '内蒙古': '内蒙古自治区', '广西': '广西壮族自治区', '西藏': '西藏自治区',\n",
    "    '宁夏': '宁夏回族自治区', '新疆': '新疆维吾尔自治区'\n",
    "}\n",
    "# 应用映射，未匹配到的暂时保留原简称（后续过滤）\n",
    "user_df['province'] = user_df['province_short'].map(standard_provinces).fillna(user_df['province_short'])\n",
    "\n",
    "# 5. 计算各省份总消费金额\n",
    "province_amount = user_consume.merge(\n",
    "    user_df[['user_id', 'province']],  # 关联用户表的“用户ID-省份”对应关系\n",
    "    on='user_id'\n",
    ").groupby('province')['consume_amount'].sum().round(0).astype(int)\n",
    "\n",
    "# 6. 过滤无效省份（只保留pyecharts地图能识别的标准省份名称）\n",
    "valid_provinces = list(standard_provinces.values())\n",
    "province_amount = province_amount[province_amount.index.isin(valid_provinces)]\n",
    "\n",
    "# 7. 转换为pyecharts所需格式：[(省份名称, 消费金额), ...]\n",
    "province_data = [(province, int(amount)) for province, amount in province_amount.items()]\n",
    "\n",
    "# 8. 验证数据（避免后续地图渲染异常）\n",
    "print(\"各省份消费金额统计（前5个）：\")\n",
    "for i, (prov, amt) in enumerate(province_data[:5]):\n",
    "    print(f\"{i+1}. {prov}: {amt:,} 元\")\n",
    "\n",
    "# 9. 绘制动态中国地图\n",
    "c = (\n",
    "    Map(\n",
    "        init_opts=opts.InitOpts(\n",
    "            theme=ThemeType.MACARONS,  # 清新配色主题（可选其他主题）\n",
    "            width=\"1200px\",            # 地图宽度（适配电脑屏幕）\n",
    "            height=\"600px\"             # 地图高度\n",
    "        )\n",
    "    )\n",
    "    .add(\n",
    "        series_name=\"总消费金额（元）\",  # 鼠标hover时显示的系列名称\n",
    "        data_pair=province_data,       # 省份-金额数据\n",
    "        maptype=\"china\",               # 地图类型：中国地图\n",
    "        label_opts=opts.LabelOpts(is_show=True),  # 显示省份名称标签\n",
    "    )\n",
    "    .set_global_opts(\n",
    "        # 标题配置\n",
    "        title_opts=opts.TitleOpts(\n",
    "            title=\"全国各省份消费金额分布（动态地图）\",\n",
    "            title_textstyle_opts=opts.TextStyleOpts(font_size=16, font_weight=\"bold\"),\n",
    "            pos_top=\"20px\",  # 标题距离顶部的距离\n",
    "        ),\n",
    "        # 视觉映射配置（颜色区分金额等级）\n",
    "        visualmap_opts=opts.VisualMapOpts(\n",
    "            max_=max(province_amount.values) * 0.8,  # 最大值（避免极值导致颜色失衡）\n",
    "            is_piecewise=True,  # 分段显示（比连续色条更易读）\n",
    "            pieces=[            # 金额分段及对应颜色、标签\n",
    "                {\"min\": 60000, \"label\": \"6万以上\", \"color\": \"#e74c3c\"},\n",
    "                {\"min\": 40000, \"max\": 59999, \"label\": \"4-6万\", \"color\": \"#f39c12\"},\n",
    "                {\"min\": 20000, \"max\": 39999, \"label\": \"2-4万\", \"color\": \"#f1c40f\"},\n",
    "                {\"min\": 10000, \"max\": 19999, \"label\": \"1-2万\", \"color\": \"#2ecc71\"},\n",
    "                {\"max\": 9999, \"label\": \"1万以下\", \"color\": \"#27ae60\"}\n",
    "            ],\n",
    "            pos_left=\"50px\",  # 视觉映射条距离左侧的距离\n",
    "            pos_bottom=\"50px\",  # 视觉映射条距离底部的距离\n",
    "        ),\n",
    "    )\n",
    ")\n",
    "\n",
    "# 10. 保存为HTML文件（双击即可打开，支持动态交互）\n",
    "c.render(\"23_动态地图_省份消费分布.html\")\n",
    "print(\"\\n动态地图生成完成！文件已保存为：23_动态地图_省份消费分布.html\")\n",
    "print(\"交互功能说明：\")\n",
    "print(\"1. 鼠标hover任意省份，可查看该省份的具体消费金额\")\n",
    "print(\"2. 点击右侧分段标签，可隐藏/显示对应金额范围的省份\")\n",
    "print(\"3. 支持缩放、平移地图，查看细节区域\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "cdafa702-9016-4548-8918-1145593c60c3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "✅ 成功读取消费表数据\n"
     ]
    }
   ],
   "source": [
    "# 1. 导入基础库（Matplotlib+Pandas，Python默认环境多已预装）\n",
    "import matplotlib.pyplot as plt\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# 2. 关键：设置中文字体（避免中文乱码，Windows/Linux/Mac通用）\n",
    "plt.rcParams['font.sans-serif'] = ['Microsoft YaHei', 'SimHei', 'Arial Unicode MS']\n",
    "plt.rcParams['axes.unicode_minus'] = False  # 解决负号显示异常\n",
    "\n",
    "# 3. 数据读取与预处理（与之前逻辑一致，确保数据连续性）\n",
    "try:\n",
    "    # 读取消费表（路径错误时给出明确解决方案）\n",
    "    consume_df = pd.read_excel('simulated_data.xlsx', sheet_name='消费表')\n",
    "    print(\"✅ 成功读取消费表数据\")\n",
    "except FileNotFoundError:\n",
    "    print(\"❌ 错误：未找到simulated_data.xlsx文件！\")\n",
    "    print(\"   请执行以下操作：\")\n",
    "    print(\"   1. 将文件放在代码同一文件夹；\")\n",
    "    print(\"   2. 或修改路径为：consume_df = pd.read_excel(r'C:\\\\Users\\\\你的用户名\\\\Desktop\\\\simulated_data.xlsx', sheet_name='消费表')\")\n",
    "    exit()\n",
    "\n",
    "# 处理时间格式，提取“年月”用于分组\n",
    "consume_df['consume_time'] = pd.to_datetime(consume_df['consume_time'], errors='coerce')\n",
    "consume_df = consume_df.dropna(subset=['consume_time'])  # 删除无效时间记录\n",
    "consume_df['consume_month'] = consume_df['consume_time'].dt.strftime('%Y-%m')\n",
    "\n",
    "# 计算月度核心指标（消费金额+订单数）\n",
    "monthly_stats = consume_df.groupby('consume_month').agg({\n",
    "    'consume_amount': 'sum',  # 月度总消费金额\n",
    "    'consume_id': 'count'     # 月度订单数（用consume_id计数，避免重复）\n",
    "}).reset_index()\n",
    "monthly_stats.columns = ['month', 'total_amount', 'order_count']\n",
    "\n",
    "# 4. 提取绘图数据（月份、金额、订单数）\n",
    "months = monthly_stats['month']  # x轴：月份\n",
    "amounts = monthly_stats['total_amount']  # 左y轴：消费金额\n",
    "orders = monthly_stats['order_count']  # 右y轴：订单数\n",
    "\n",
    "# 5. 绘制双轴折线图（Matplotlib核心代码）\n",
    "fig, ax1 = plt.subplots(figsize=(12, 7))  # 创建画布和左y轴\n",
    "\n",
    "# 5.1 左y轴：消费金额（蓝色线条+圆点标记）\n",
    "color1 = '#3498db'  # 蓝色\n",
    "ax1.set_xlabel('月份', fontsize=12, fontweight='bold')\n",
    "ax1.set_ylabel('消费金额（元）', color=color1, fontsize=12, fontweight='bold')\n",
    "line1 = ax1.plot(\n",
    "    months, amounts, \n",
    "    color=color1, marker='o', markersize=6,  # 圆点标记\n",
    "    linewidth=2, label='月度消费金额'\n",
    ")\n",
    "ax1.tick_params(axis='y', labelcolor=color1)  # 左y轴标签颜色\n",
    "# 旋转x轴月份标签，避免重叠\n",
    "plt.xticks(rotation=45, ha='right')  # ha='right'：标签右对齐，更美观\n",
    "\n",
    "# 5.2 右y轴：订单数（红色线条+方形标记）\n",
    "ax2 = ax1.twinx()  # 创建共享x轴的右y轴\n",
    "color2 = '#e74c3c'  # 红色\n",
    "ax2.set_ylabel('订单数', color=color2, fontsize=12, fontweight='bold')\n",
    "line2 = ax2.plot(\n",
    "    months, orders, \n",
    "    color=color2, marker='s', markersize=6,  # 方形标记\n",
    "    linewidth=2, label='月度订单数'\n",
    ")\n",
    "ax2.tick_params(axis='y', labelcolor=color2)  # 右y轴标签颜色\n",
    "\n",
    "# 5.3 添加关键标注（提升可读性，替代pyecharts交互）\n",
    "# 标注消费金额最高/最低月份\n",
    "max_idx = amounts.idxmax()\n",
    "min_idx = amounts.idxmin()\n",
    "ax1.annotate(\n",
    "    f'最高：{int(amounts[max_idx]):,}元', \n",
    "    xy=(months[max_idx], amounts[max_idx]),\n",
    "    xytext=(0, 10), textcoords='offset points',  # 向上偏移10点\n",
    "    bbox=dict(boxstyle='round,pad=0.3', facecolor=color1, alpha=0.7),\n",
    "    fontsize=9, color='white'\n",
    ")\n",
    "ax1.annotate(\n",
    "    f'最低：{int(amounts[min_idx]):,}元', \n",
    "    xy=(months[min_idx], amounts[min_idx]),\n",
    "    xytext=(0, -15), textcoords='offset points',  # 向下偏移15点\n",
    "    bbox=dict(boxstyle='round,pad=0.3', facecolor=color1, alpha=0.7),\n",
    "    fontsize=9, color='white'\n",
    ")\n",
    "\n",
    "# 标注订单数平均值线\n",
    "order_avg = orders.mean()\n",
    "ax2.axhline(y=order_avg, color=color2, linestyle='--', alpha=0.8, linewidth=1.5)\n",
    "ax2.annotate(\n",
    "    f'月均：{int(order_avg)}单', \n",
    "    xy=(months.iloc[-1], order_avg),\n",
    "    xytext=(0, -15), textcoords='offset points',\n",
    "    bbox=dict(boxstyle='round,pad=0.3', facecolor=color2, alpha=0.7),\n",
    "    fontsize=9, color='white'\n",
    ")\n",
    "\n",
    "# 5.4 添加图例（区分两条线）\n",
    "lines1, labels1 = ax1.get_legend_handles_labels()\n",
    "lines2, labels2 = ax2.get_legend_handles_labels()\n",
    "ax1.legend(lines1 + lines2, labels1 + labels2, loc='upper left', fontsize=10)\n",
    "\n",
    "# 5.5 整体美化（网格、标题）\n",
    "ax1.grid(True, alpha=0.3, linestyle='--')  # 浅色虚线网格\n",
    "plt.title('月度消费金额与订单数趋势分析', fontsize=14, fontweight='bold', pad=20)\n",
    "plt.tight_layout()  # 自动调整布局，避免标签被截断\n",
    "\n",
    "# 6. 保存高分辨率图片（可直接插入报告）\n",
    "plt.savefig(\n",
    "    '24_月度消费趋势双轴图.png',\n",
    "    dpi=300,  # 高分辨率（300dpi，适合印刷/电子报告）\n",
    "    bbox_inches='tight',  # 紧凑布局，避免边缘被裁剪\n",
    "    facecolor='white'     # 背景色为白色，避免透明背景\n",
    ")\n",
    "plt.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "e0b5ce13-92a8-409e-aaef-70988615b1bc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "✅ 成功读取用户表和消费表\n",
      "\n",
      "🎉 交互式热力图生成完成！\n",
      "📁 文件：25_交互式热力图_周时段消费.html\n",
      "🔧 交互功能：\n",
      "   1. 鼠标hover任意单元格 → 查看具体“星期-时段”的消费金额；\n",
      "   2. 右侧颜色条 → 对照金额与颜色的对应关系；\n",
      "   3. 支持缩放、下载图片（点击右上角相机图标）。\n",
      "\n",
      "📊 数据概览：\n",
      "   - 总消费记录数：1194条\n",
      "   - 最高消费时段：下午（44,276元）\n"
     ]
    }
   ],
   "source": [
    "# 1. 导入必需库（Plotly用于交互式热力图，Pandas/Numpy处理数据）\n",
    "import plotly.express as px\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# 2. 关键步骤1：读取基础数据（用户表+消费表）\n",
    "try:\n",
    "    user_df = pd.read_excel('simulated_data.xlsx', sheet_name='用户表')\n",
    "    consume_df = pd.read_excel('simulated_data.xlsx', sheet_name='消费表')\n",
    "    print(\"✅ 成功读取用户表和消费表\")\n",
    "except FileNotFoundError:\n",
    "    print(\"❌ 错误：未找到simulated_data.xlsx文件！\")\n",
    "    print(\"   解决方案：将文件放在代码同一文件夹，或修改路径为：\")\n",
    "    print(\"   user_df = pd.read_excel(r'C:\\\\Users\\\\你的用户名\\\\Desktop\\\\simulated_data.xlsx', sheet_name='用户表')\")\n",
    "    exit()\n",
    "\n",
    "# 3. 关键步骤2：合并数据生成user_consume（包含consume_time字段）\n",
    "user_consume = pd.merge(\n",
    "    left=consume_df,                # 左表：消费表（含consume_time）\n",
    "    right=user_df[['user_id']],     # 右表：用户表（仅需user_id关联，避免冗余）\n",
    "    on='user_id',                   # 关联字段：用户ID\n",
    "    how='left'                      # 左连接：保留所有消费记录\n",
    ")\n",
    "\n",
    "# 4. 数据预处理：确保consume_time为datetime类型（避免提取星期报错）\n",
    "user_consume['consume_time'] = pd.to_datetime(user_consume['consume_time'], errors='coerce')\n",
    "# 删除无效时间记录（避免后续分组异常）\n",
    "user_consume = user_consume.dropna(subset=['consume_time'])\n",
    "\n",
    "# 5. 添加时段信息（星期+消费时段）\n",
    "# 5.1 提取星期（0=周一，6=周日），并映射为中文标签\n",
    "user_consume['weekday'] = user_consume['consume_time'].dt.weekday  # 数字星期\n",
    "weekday_map = {0: '周一', 1: '周二', 2: '周三', 3: '周四', 4: '周五', 5: '周六', 6: '周日'}\n",
    "user_consume['weekday_label'] = user_consume['weekday'].map(weekday_map)\n",
    "\n",
    "# 5.2 按概率分配消费时段（凌晨/上午/下午/晚上）\n",
    "time_period_probs = {'凌晨': 0.1, '上午': 0.3, '下午': 0.4, '晚上': 0.2}  # 时段概率\n",
    "user_consume['time_period'] = np.random.choice(\n",
    "    list(time_period_probs.keys()),\n",
    "    size=len(user_consume),\n",
    "    p=list(time_period_probs.values())\n",
    ")\n",
    "\n",
    "# 6. 计算周-时段消费金额（热力图核心数据）\n",
    "# 按“星期标签+时段”分组，汇总消费金额\n",
    "weekday_period_amount = user_consume.groupby(\n",
    "    ['weekday_label', 'time_period']\n",
    ")['consume_amount'].sum().unstack(fill_value=0)\n",
    "\n",
    "# 调整时段顺序（按逻辑顺序排列：凌晨→上午→下午→晚上）\n",
    "time_period_order = ['凌晨', '上午', '下午', '晚上']\n",
    "weekday_period_amount = weekday_period_amount.reindex(columns=time_period_order)\n",
    "\n",
    "# 调整星期顺序（确保从周一到周日排列）\n",
    "weekday_order = ['周一', '周二', '周三', '周四', '周五', '周六', '周日']\n",
    "weekday_period_amount = weekday_period_amount.reindex(index=weekday_order)\n",
    "\n",
    "# 7. 绘制交互式热力图（Plotly实现，支持hover查看金额）\n",
    "fig = px.imshow(\n",
    "    weekday_period_amount,  # 热力图数据（行=星期，列=时段）\n",
    "    labels=dict(\n",
    "        x=\"消费时段\", \n",
    "        y=\"星期\", \n",
    "        color=\"消费金额（元）\"  # 颜色映射含义\n",
    "    ),\n",
    "    x=weekday_period_amount.columns,  # x轴：时段\n",
    "    y=weekday_period_amount.index,    # y轴：星期\n",
    "    color_continuous_scale='YlOrRd',  # 颜色方案（黄→橙→红，高金额更醒目）\n",
    "    title='周-时段消费金额分布（交互式热力图）',\n",
    "    width=900,  # 图表宽度\n",
    "    height=600  # 图表高度\n",
    ")\n",
    "\n",
    "# 8. 美化图表（提升交互体验）\n",
    "fig.update_layout(\n",
    "    xaxis_title='消费时段',  # x轴标题\n",
    "    yaxis_title='星期',      # y轴标题\n",
    "    title_font=dict(size=16, weight='bold'),  # 标题样式\n",
    "    xaxis_tickfont=dict(size=11),  # x轴标签字体\n",
    "    yaxis_tickfont=dict(size=11),  # y轴标签字体\n",
    "    coloraxis_colorbar=dict(\n",
    "        title=\"消费金额（元）\",  # 颜色条标题\n",
    "        title_font=dict(size=10),\n",
    "        tickfont=dict(size=9)\n",
    "    )\n",
    ")\n",
    "\n",
    "# 9. 保存为HTML文件（双击打开即可交互）\n",
    "fig.write_html('25_交互式热力图_周时段消费.html')\n",
    "print(\"\\n🎉 交互式热力图生成完成！\")\n",
    "print(\"📁 文件：25_交互式热力图_周时段消费.html\")\n",
    "print(\"🔧 交互功能：\")\n",
    "print(\"   1. 鼠标hover任意单元格 → 查看具体“星期-时段”的消费金额；\")\n",
    "print(\"   2. 右侧颜色条 → 对照金额与颜色的对应关系；\")\n",
    "print(\"   3. 支持缩放、下载图片（点击右上角相机图标）。\")\n",
    "\n",
    "# 打印数据概览（验证热力图数据合理性）\n",
    "print(f\"\\n📊 数据概览：\")\n",
    "print(f\"   - 总消费记录数：{len(user_consume)}条\")\n",
    "print(f\"   - 最高消费时段：{weekday_period_amount.max().idxmax()}（{weekday_period_amount.max().max():,.0f}元）\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "e57421fa-d876-4cb3-b53a-01f0de39d12a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "✅ 成功读取用户表和消费表\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\29815\\AppData\\Local\\Temp\\ipykernel_14984\\2182899021.py:48: UserWarning:\n",
      "\n",
      "Setting the 'color' property will override the edgecolor or facecolor properties.\n",
      "\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "🎉 核心指标看板生成完成！\n",
      "📁 保存文件：26_多子图看板_核心指标.png\n",
      "📊 看板包含关键信息：\n",
      "   - 总消费金额：613,207 元 | 总用户数：200 人 | 总订单数：1194 单\n",
      "   - TOP1消费品类：服装鞋帽（158,710 元）\n",
      "   - 主要支付方式：微信支付（44.1%）\n"
     ]
    }
   ],
   "source": [
    "# 1. 导入必需库（Matplotlib绘图，Pandas处理数据）\n",
    "import matplotlib.pyplot as plt\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# 2. 关键：设置中文字体（避免中文乱码）\n",
    "plt.rcParams['font.sans-serif'] = ['Microsoft YaHei', 'SimHei', 'Arial Unicode MS']\n",
    "plt.rcParams['axes.unicode_minus'] = False\n",
    "\n",
    "# 3. 步骤1：读取基础数据（用户表+消费表）\n",
    "try:\n",
    "    user_df = pd.read_excel('simulated_data.xlsx', sheet_name='用户表')\n",
    "    consume_df = pd.read_excel('simulated_data.xlsx', sheet_name='消费表')\n",
    "    print(\"✅ 成功读取用户表和消费表\")\n",
    "except FileNotFoundError:\n",
    "    print(\"❌ 错误：未找到simulated_data.xlsx文件！\")\n",
    "    print(\"   解决方案：将文件放在代码同一文件夹，或修改路径为：\")\n",
    "    print(\"   user_df = pd.read_excel(r'C:\\\\Users\\\\你的用户名\\\\Desktop\\\\simulated_data.xlsx', sheet_name='用户表')\")\n",
    "    exit()\n",
    "\n",
    "# 4. 步骤2：计算所有缺失的核心变量（关键修复部分）\n",
    "# 4.1 category_amount：各消费品类总金额（用于TOP1品类和TOP5柱状图）\n",
    "category_amount = consume_df.groupby('consume_category')['consume_amount'].sum().sort_values(ascending=False)\n",
    "# 4.2 payment_count：各支付方式使用次数（用于支付方式饼图）\n",
    "payment_count = consume_df['payment_method'].value_counts()\n",
    "# 4.3 monthly_order：月度订单数（用于月度趋势图）\n",
    "consume_df['consume_time'] = pd.to_datetime(consume_df['consume_time'], errors='coerce')\n",
    "consume_df['consume_month'] = consume_df['consume_time'].dt.strftime('%Y-%m')\n",
    "monthly_order = consume_df.groupby('consume_month').size()  # 订单数=消费记录条数\n",
    "\n",
    "# 5. 步骤3：计算核心指标（含修复的top_category）\n",
    "total_amount = consume_df['consume_amount'].sum()          # 总消费金额\n",
    "total_order = len(consume_df)                              # 总订单数\n",
    "total_user = user_df['user_id'].nunique()                  # 总用户数（去重）\n",
    "avg_amount = consume_df['consume_amount'].mean()           # 平均订单金额\n",
    "top_category = category_amount.index[0]                   # TOP1消费品类（修复后可正常使用）\n",
    "avg_user_order = total_order / total_user                  # 补充：人均订单数（提升看板丰富度）\n",
    "\n",
    "# 6. 步骤4：创建2x2多子图看板\n",
    "fig, ((ax1, ax2), (ax3, ax4)) = plt.subplots(2, 2, figsize=(16, 12))\n",
    "fig.suptitle('消费数据核心指标看板', fontsize=16, fontweight='bold', y=0.95)  # 总标题\n",
    "\n",
    "# 子图1：核心指标卡片（总金额+人均指标）\n",
    "ax1.set_xlim(0, 1)\n",
    "ax1.set_ylim(0, 1)\n",
    "ax1.axis('off')  # 隐藏坐标轴\n",
    "# 添加卡片背景\n",
    "ax1.add_patch(plt.Rectangle((0.05, 0.05), 0.9, 0.9, fill=True, color='#f8f9fa', edgecolor='#dee2e6', linewidth=2))\n",
    "# 写入指标文本\n",
    "ax1.text(0.5, 0.8, '总消费金额', ha='center', va='center', fontsize=14, fontweight='bold', transform=ax1.transAxes)\n",
    "ax1.text(0.5, 0.55, f'{total_amount:,.0f} 元', ha='center', va='center', fontsize=18, color='#e74c3c', fontweight='bold', transform=ax1.transAxes)\n",
    "ax1.text(0.5, 0.35, f'人均消费：{total_amount/total_user:.0f} 元 | 人均订单：{avg_user_order:.1f} 单', ha='center', va='center', fontsize=12, transform=ax1.transAxes)\n",
    "ax1.text(0.5, 0.2, f'平均订单金额：{avg_amount:.0f} 元', ha='center', va='center', fontsize=12, transform=ax1.transAxes)\n",
    "\n",
    "# 子图2：主要支付方式占比饼图（取前3种，避免饼图过于拥挤）\n",
    "payment_top3 = payment_count.head(3)\n",
    "colors_pie = ['#f39c12', '#2ecc71', '#9b59b6']  # 橙、绿、紫配色（醒目且区分度高）\n",
    "wedges, texts, autotexts = ax2.pie(\n",
    "    payment_top3.values, \n",
    "    labels=payment_top3.index, \n",
    "    autopct='%1.1f%%',  # 显示百分比（保留1位小数）\n",
    "    colors=colors_pie, \n",
    "    startangle=90,       # 从90度开始（顶部为第一个扇区）\n",
    "    textprops={'fontsize': 10}\n",
    ")\n",
    "ax2.set_title('主要支付方式占比', fontsize=12, fontweight='bold', pad=20)\n",
    "\n",
    "# 子图3：TOP5消费品类金额（横向柱状图，便于查看品类名称）\n",
    "top5_category = category_amount.head(5)\n",
    "colors_bar = '#3498db'  # 蓝色（专业且清晰）\n",
    "bars = ax3.barh(range(len(top5_category)), top5_category.values, color=colors_bar, alpha=0.8)\n",
    "# 设置y轴标签（品类名称）\n",
    "ax3.set_yticks(range(len(top5_category)))\n",
    "ax3.set_yticklabels(top5_category.index, fontsize=10)\n",
    "# 设置x轴和标题\n",
    "ax3.set_xlabel('消费金额（元）', fontsize=11)\n",
    "ax3.set_title(f'TOP5消费品类（TOP1：{top_category}）', fontsize=12, fontweight='bold', pad=20)\n",
    "# 添加数值标签（显示具体金额）\n",
    "for i, (bar, val) in enumerate(zip(bars, top5_category.values)):\n",
    "    ax3.text(val + total_amount*0.01, i, f'{val:,.0f}', va='center', fontsize=10, fontweight='500')\n",
    "# 添加网格线（辅助读数）\n",
    "ax3.grid(axis='x', alpha=0.3, linestyle='--')\n",
    "\n",
    "# 子图4：月度订单数趋势（折线图+圆点标记）\n",
    "ax4.plot(\n",
    "    range(len(monthly_order)),  # 用索引代替月份字符串，避免x轴拥挤\n",
    "    monthly_order.values, \n",
    "    color='#27ae60',  # 绿色（代表增长/趋势）\n",
    "    linewidth=2.5, \n",
    "    marker='o',       # 圆点标记（突出每月数据）\n",
    "    markersize=5, \n",
    "    markerfacecolor='#27ae60', \n",
    "    markeredgecolor='white', \n",
    "    markeredgewidth=1\n",
    ")\n",
    "# 设置x轴标签（只显示奇数索引的月份，避免重叠）\n",
    "ax4.set_xticks(range(0, len(monthly_order), 2))\n",
    "ax4.set_xticklabels(monthly_order.index[::2], rotation=45, ha='right', fontsize=9)\n",
    "# 设置y轴和标题\n",
    "ax4.set_ylabel('订单数', fontsize=11)\n",
    "ax4.set_title('月度订单数趋势', fontsize=12, fontweight='bold', pad=20)\n",
    "# 添加网格线\n",
    "ax4.grid(True, alpha=0.3, linestyle='--')\n",
    "\n",
    "# 7. 调整布局并保存看板\n",
    "plt.tight_layout()  # 自动调整子图间距\n",
    "plt.subplots_adjust(top=0.92)  # 预留顶部空间给总标题\n",
    "plt.savefig(\n",
    "    '26_多子图看板_核心指标.png',\n",
    "    dpi=300,                # 高分辨率（300dpi，适合报告/PPT）\n",
    "    bbox_inches='tight',    # 紧凑布局，避免边缘被裁剪\n",
    "    facecolor='white'       # 背景色为白色（避免透明背景）\n",
    ")\n",
    "plt.close()\n",
    "\n",
    "# 打印结果提示\n",
    "print(\"\\n🎉 核心指标看板生成完成！\")\n",
    "print(\"📁 保存文件：26_多子图看板_核心指标.png\")\n",
    "print(f\"📊 看板包含关键信息：\")\n",
    "print(f\"   - 总消费金额：{total_amount:,.0f} 元 | 总用户数：{total_user} 人 | 总订单数：{total_order} 单\")\n",
    "print(f\"   - TOP1消费品类：{top_category}（{category_amount.iloc[0]:,.0f} 元）\")\n",
    "print(f\"   - 主要支付方式：{payment_top3.index[0]}（{payment_top3.iloc[0]/payment_count.sum()*100:.1f}%）\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "30a2aa44-bbf7-4c55-984c-8b39e91eaf3b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "✅ 成功读取消费表数据\n",
      "\n",
      "📊 月度目标达成详情：\n",
      "   月度目标金额：80,000 元\n",
      "   最近一个月实际金额：38,264 元\n",
      "   目标达成率：47.8%\n",
      "\n",
      "🎉 交互式仪表盘生成完成！\n",
      "📁 保存文件：27_仪表盘_月度目标达成率_Plotly.html\n",
      "🔧 交互功能：\n",
      "   1. 页面加载后自动显示达成率数字和仪表盘；\n",
      "   2. 支持缩放、下载图片（点击右上角相机图标）；\n",
      "   3. 鼠标hover仪表盘，可查看精确达成率数值。\n"
     ]
    }
   ],
   "source": [
    "# 1. 导入仅需的库（Plotly+Pandas，安装简单且版本兼容）\n",
    "import plotly.graph_objects as go\n",
    "import pandas as pd\n",
    "\n",
    "# 2. 第一步：读取数据并计算月度消费金额（核心逻辑不变）\n",
    "try:\n",
    "    # 读取消费表（确保文件路径正确，Windows用r前缀或双反斜杠）\n",
    "    consume_df = pd.read_excel('simulated_data.xlsx', sheet_name='消费表')\n",
    "    print(\"✅ 成功读取消费表数据\")\n",
    "except FileNotFoundError:\n",
    "    print(\"❌ 错误：未找到simulated_data.xlsx文件！\")\n",
    "    print(\"   解决方案：\")\n",
    "    print(\"   1. 将文件放在代码同一文件夹；\")\n",
    "    print(\"   2. 或修改路径为：consume_df = pd.read_excel(r'C:\\\\Users\\\\你的用户名\\\\Desktop\\\\simulated_data.xlsx', sheet_name='消费表')\")\n",
    "    exit()\n",
    "\n",
    "# 处理时间格式，提取“年月”并按月份汇总金额\n",
    "consume_df['consume_time'] = pd.to_datetime(consume_df['consume_time'], errors='coerce')\n",
    "consume_df = consume_df.dropna(subset=['consume_time'])  # 删除无效时间记录\n",
    "consume_df['consume_month'] = consume_df['consume_time'].dt.strftime('%Y-%m')\n",
    "# 计算月度消费金额并按时间排序（确保取到最近一个月）\n",
    "monthly_amount = consume_df.groupby('consume_month')['consume_amount'].sum().sort_index()\n",
    "\n",
    "# 3. 第二步：计算月度目标达成率\n",
    "monthly_target = 80000  # 设定月度消费目标（可按需调整）\n",
    "latest_month_amount = monthly_amount.iloc[-1]  # 最近一个月实际金额\n",
    "achievement_rate = (latest_month_amount / monthly_target) * 100  # 达成率（百分比）\n",
    "# 限制达成率范围（0%-150%），避免仪表盘溢出\n",
    "achievement_rate = max(0, min(achievement_rate, 150))\n",
    "\n",
    "# 打印目标达成情况（验证数据）\n",
    "print(f\"\\n📊 月度目标达成详情：\")\n",
    "print(f\"   月度目标金额：{monthly_target:,.0f} 元\")\n",
    "print(f\"   最近一个月实际金额：{latest_month_amount:,.0f} 元\")\n",
    "print(f\"   目标达成率：{achievement_rate:.1f}%\")\n",
    "\n",
    "# 4. 第三步：用Plotly绘制交互式仪表盘（核心代码）\n",
    "fig = go.Figure()\n",
    "\n",
    "# 4.1 添加仪表盘背景（三色渐变，对应低/中/高达成率）\n",
    "# 定义仪表盘角度范围（Plotly仪表盘默认0°在右侧，270°在底部，需适配视觉习惯）\n",
    "# 0%-150% 对应角度：270°（底部）→ 90°（顶部）\n",
    "fig.add_trace(go.Indicator(\n",
    "    mode=\"gauge+number\",  # 显示仪表盘+数字\n",
    "    value=achievement_rate,  # 核心值：达成率\n",
    "    title={\n",
    "        \"text\": f\"月度消费目标达成率\\n目标：{monthly_target:,.0f}元 | 实际：{latest_month_amount:,.0f}元\",\n",
    "        \"font\": {\"size\": 16, \"weight\": \"bold\", \"family\": \"Arial\"}\n",
    "    },\n",
    "    number={\n",
    "        \"font\": {\"size\": 24, \"weight\": \"bold\", \"color\": \"#e74c3c\"},  # 达成率数字样式\n",
    "        \"suffix\": \"%\"  # 数字后缀：百分比\n",
    "    },\n",
    "    gauge={\n",
    "        # 仪表盘范围（0%-150%）\n",
    "        \"axis\": {\"range\": [0, 150], \"tickwidth\": 1, \"tickcolor\": \"#333\"},\n",
    "        # 仪表盘颜色分区（三色渐变）\n",
    "        \"bar\": {\"color\": \"#333\"},  # 中心指针颜色\n",
    "        \"bgcolor\": \"white\",  # 背景色\n",
    "        \"steps\": [\n",
    "            {\"range\": [0, 30], \"color\": \"#67e0e3\"},  # 0%-30%：浅蓝（低达成）\n",
    "            {\"range\": [30, 70], \"color\": \"#37a2da\"},  # 30%-70%：蓝色（中达成）\n",
    "            {\"range\": [70, 150], \"color\": \"#fd666d\"}  # 70%-150%：红色（高达成）\n",
    "        ],\n",
    "        # 仪表盘边界样式\n",
    "        \"borderwidth\": 2,\n",
    "        \"bordercolor\": \"#ddd\"\n",
    "    },\n",
    "    domain={\"x\": [0.1, 0.9], \"y\": [0.1, 0.9]}  # 仪表盘在画布中的位置（避免边缘裁剪）\n",
    "))\n",
    "\n",
    "# 4.2 美化仪表盘布局\n",
    "fig.update_layout(\n",
    "    width=800,  # 仪表盘宽度\n",
    "    height=600,  # 仪表盘高度\n",
    "    paper_bgcolor=\"white\",  # 画布背景色\n",
    "    margin=dict(l=50, r=50, t=100, b=50)  # 边距（避免标题/数字被裁剪）\n",
    ")\n",
    "\n",
    "# 5. 保存为HTML文件（交互式，双击即可打开）\n",
    "fig.write_html(\"27_仪表盘_月度目标达成率_Plotly.html\")\n",
    "print(\"\\n🎉 交互式仪表盘生成完成！\")\n",
    "print(\"📁 保存文件：27_仪表盘_月度目标达成率_Plotly.html\")\n",
    "print(\"🔧 交互功能：\")\n",
    "print(\"   1. 页面加载后自动显示达成率数字和仪表盘；\")\n",
    "print(\"   2. 支持缩放、下载图片（点击右上角相机图标）；\")\n",
    "print(\"   3. 鼠标hover仪表盘，可查看精确达成率数值。\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "ba3f1086-8802-4eb5-9cda-a852bbdc5d74",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "✅ 成功读取用户表\n",
      "\n",
      "🎉 用户画像看板生成完成！\n",
      "📁 文件：28_用户画像看板.html（双击打开即可交互）\n",
      "🔧 交互功能：hover查看数值、缩放、下载图片\n",
      "\n",
      "📊 数据概览：\n",
      "   - 有效用户：200人 | 年龄范围：18-64岁\n",
      "   - 性别分布：{'女': 101, '男': 99} | 注册时间：2022-12至2025-11\n"
     ]
    }
   ],
   "source": [
    "# 1. 导入必需库（适配旧版Plotly，仅用基础组件）\n",
    "import plotly.graph_objects as go\n",
    "from plotly.subplots import make_subplots\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# 2. 读取并预处理用户表数据\n",
    "try:\n",
    "    user_df = pd.read_excel('simulated_data.xlsx', sheet_name='用户表')\n",
    "    print(\"✅ 成功读取用户表\")\n",
    "except FileNotFoundError:\n",
    "    print(\"❌ 错误：未找到simulated_data.xlsx！\")\n",
    "    print(\"   解决方案：将文件放在代码同一文件夹，或修改路径为：\")\n",
    "    print(\"   user_df = pd.read_excel(r'C:\\\\Users\\\\你的用户名\\\\Desktop\\\\simulated_data.xlsx', sheet_name='用户表')\")\n",
    "    exit()\n",
    "\n",
    "# 数据预处理（确保无异常值）\n",
    "user_df['register_time'] = pd.to_datetime(user_df['register_time'], errors='coerce')\n",
    "user_df = user_df.dropna(subset=['age', 'gender', 'city', 'register_time'])  # 删除缺失值\n",
    "user_df = user_df[(user_df['age'] >= 0) & (user_df['age'] <= 100)]  # 过滤异常年龄\n",
    "\n",
    "# 3. 创建2x2多子图布局（关键修复：将'line'改为'scatter'）\n",
    "fig = make_subplots(\n",
    "    rows=2, cols=2,\n",
    "    subplot_titles=(\n",
    "        '用户年龄分布', \n",
    "        '用户性别占比', \n",
    "        '各城市用户数TOP8', \n",
    "        '用户注册月度趋势'\n",
    "    ),\n",
    "    # 修复：第2行第2列用'scatter'替代'line'，旧版Plotly支持\n",
    "    specs=[\n",
    "        [{'type': 'histogram'}, {'type': 'pie'}],\n",
    "        [{'type': 'bar'}, {'type': 'scatter'}]  # 关键修改点\n",
    "    ],\n",
    "    vertical_spacing=0.2,\n",
    "    horizontal_spacing=0.15\n",
    ")\n",
    "\n",
    "# 4. 子图1：用户年龄分布（直方图）\n",
    "fig.add_trace(\n",
    "    go.Histogram(\n",
    "        x=user_df['age'],\n",
    "        nbinsx=12,\n",
    "        marker_color='#3498db',\n",
    "        marker_line_color='#2980b9',\n",
    "        marker_line_width=1\n",
    "    ),\n",
    "    row=1, col=1\n",
    ")\n",
    "fig.update_xaxes(title_text='年龄', row=1, col=1)\n",
    "fig.update_yaxes(title_text='用户数', row=1, col=1)\n",
    "\n",
    "# 5. 子图2：用户性别占比（饼图）\n",
    "gender_count = user_df['gender'].value_counts()\n",
    "fig.add_trace(\n",
    "    go.Pie(\n",
    "        labels=gender_count.index,\n",
    "        values=gender_count.values,\n",
    "        marker_colors=['#3498db', '#e74c3c'],\n",
    "        textinfo='percent+label',\n",
    "        textfont=dict(size=11)\n",
    "    ),\n",
    "    row=1, col=2\n",
    ")\n",
    "\n",
    "# 6. 子图3：各城市用户数TOP8（柱状图）\n",
    "city_user = user_df['city'].value_counts().head(8)\n",
    "fig.add_trace(\n",
    "    go.Bar(\n",
    "        x=city_user.index,\n",
    "        y=city_user.values,\n",
    "        marker_color='#2ecc71',\n",
    "        marker_line_color='#27ae60',\n",
    "        marker_line_width=1\n",
    "    ),\n",
    "    row=2, col=1\n",
    ")\n",
    "fig.update_xaxes(title_text='城市', tickangle=-45, row=2, col=1)\n",
    "fig.update_yaxes(title_text='用户数', row=2, col=1)\n",
    "\n",
    "# 7. 子图4：用户注册月度趋势（关键修复：用scatter+mode='lines'实现折线图）\n",
    "monthly_register = user_df.groupby(\n",
    "    user_df['register_time'].dt.to_period('M')\n",
    ").size()\n",
    "monthly_register.index = monthly_register.index.to_timestamp()  # 转换时间格式\n",
    "\n",
    "fig.add_trace(\n",
    "    go.Scatter(  # 旧版用Scatter类型\n",
    "        x=monthly_register.index,\n",
    "        y=monthly_register.values,\n",
    "        mode='lines+markers',  # 模式：显示线条+数据点（模拟折线图）\n",
    "        marker_color='#f39c12',\n",
    "        marker=dict(size=6, symbol='circle'),  # 数据点样式\n",
    "        line_width=2,  # 线条宽度\n",
    "        name='注册用户数'\n",
    "    ),\n",
    "    row=2, col=2\n",
    ")\n",
    "fig.update_xaxes(title_text='注册月份', tickformat='%Y-%m', tickangle=-45, row=2, col=2)\n",
    "fig.update_yaxes(title_text='注册用户数', row=2, col=2)\n",
    "\n",
    "# 8. 整体美化布局\n",
    "fig.update_layout(\n",
    "    height=800,\n",
    "    width=1200,\n",
    "    title_text=\"用户画像综合分析看板\",\n",
    "    title_font=dict(size=18, weight='bold'),\n",
    "    title_x=0.5,\n",
    "    paper_bgcolor='white',\n",
    "    margin=dict(l=50, r=50, t=100, b=50),\n",
    "    showlegend=False  # 子图无重复图例，隐藏全局图例\n",
    ")\n",
    "\n",
    "# 9. 保存并提示结果\n",
    "fig.write_html('28_用户画像看板.html')\n",
    "print(\"\\n🎉 用户画像看板生成完成！\")\n",
    "print(\"📁 文件：28_用户画像看板.html（双击打开即可交互）\")\n",
    "print(\"🔧 交互功能：hover查看数值、缩放、下载图片\")\n",
    "\n",
    "# 数据概览\n",
    "print(f\"\\n📊 数据概览：\")\n",
    "print(f\"   - 有效用户：{len(user_df)}人 | 年龄范围：{user_df['age'].min()}-{user_df['age'].max()}岁\")\n",
    "print(f\"   - 性别分布：{dict(gender_count)} | 注册时间：{monthly_register.index.min().strftime('%Y-%m')}至{monthly_register.index.max().strftime('%Y-%m')}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "86d9f604-e040-40e2-be78-cdd0fad12ecb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "✅ 成功读取用户表和消费表\n",
      "\n",
      "🎉 用户转化漏斗图生成完成！\n",
      "📁 文件：29_漏斗图_用户转化漏斗.html（双击打开即可交互）\n",
      "\n",
      "📊 转化漏斗详情：\n",
      "   1. 注册用户：200人（100.0%）\n",
      "   2. 浏览用户：170人（85.0%）\n",
      "   3. 加购用户：120人（60.0%）\n",
      "   4. 下单用户：90人（45.0%）\n",
      "   5. 支付用户：200人（100.0%）\n",
      "   💡 最终支付转化率：100.0%\n"
     ]
    }
   ],
   "source": [
    "# 1. 导入必需库（仅用基础组件，避免新增类依赖）\n",
    "from pyecharts.charts import Funnel\n",
    "from pyecharts import options as opts\n",
    "from pyecharts.globals import ThemeType\n",
    "import pandas as pd\n",
    "\n",
    "# 2. 步骤1：读取基础数据（用户表+消费表）\n",
    "try:\n",
    "    user_df = pd.read_excel('simulated_data.xlsx', sheet_name='用户表')\n",
    "    consume_df = pd.read_excel('simulated_data.xlsx', sheet_name='消费表')\n",
    "    print(\"✅ 成功读取用户表和消费表\")\n",
    "except FileNotFoundError:\n",
    "    print(\"❌ 错误：未找到simulated_data.xlsx文件！\")\n",
    "    print(\"   解决方案：\")\n",
    "    print(\"   1. 将文件放在代码同一文件夹；\")\n",
    "    print(\"   2. 或修改路径为：\")\n",
    "    print(\"      user_df = pd.read_excel(r'C:\\\\Users\\\\你的用户名\\\\Desktop\\\\simulated_data.xlsx', sheet_name='用户表')\")\n",
    "    print(\"      consume_df = pd.read_excel(r'C:\\\\Users\\\\你的用户名\\\\Desktop\\\\simulated_data.xlsx', sheet_name='消费表')\")\n",
    "    exit()\n",
    "\n",
    "# 3. 步骤2：计算核心指标（总注册用户+支付用户）\n",
    "total_user = user_df['user_id'].nunique()  # 总注册用户数（去重）\n",
    "paid_user = consume_df['user_id'].nunique()  # 实际支付用户数（去重）\n",
    "# 避免0值导致计算错误\n",
    "total_user = max(total_user, 1)\n",
    "paid_user = max(paid_user, 1)\n",
    "\n",
    "# 4. 步骤3：构建转化漏斗数据（注册→浏览→加购→下单→支付）\n",
    "funnel_data = [\n",
    "    (\"注册用户\", total_user),\n",
    "    (\"浏览用户\", int(total_user * 0.85)),  # 85%转化\n",
    "    (\"加购用户\", int(total_user * 0.6)),   # 60%转化\n",
    "    (\"下单用户\", int(total_user * 0.45)),  # 45%转化\n",
    "    (\"支付用户\", paid_user)                # 实际支付用户\n",
    "]\n",
    "\n",
    "# 5. 步骤4：绘制漏斗图（兼容新旧版，无GradientColor依赖）\n",
    "c = (\n",
    "    Funnel(\n",
    "        init_opts=opts.InitOpts(\n",
    "            theme=ThemeType.MACARONS,  # 旧版原生支持的主题，兼容性100%\n",
    "            width=\"1000px\",\n",
    "            height=\"600px\"\n",
    "        )\n",
    "    )\n",
    "    .add(\n",
    "        series_name=\"用户消费转化漏斗\",\n",
    "        data_pair=funnel_data,\n",
    "        sort_=\"descending\",  # 自上而下按数据降序排列（漏斗形状）\n",
    "        gap=3,               # 各层间距，避免粘连\n",
    "        # 标签配置（显示名称、人数、转化率，旧版支持）\n",
    "        label_opts=opts.LabelOpts(\n",
    "            position=\"inside\",  # 标签在漏斗内部\n",
    "            formatter=\"{b}\\n{c}人\\n({d}%)\",  # 格式：名称+人数+百分比\n",
    "            font_size=11\n",
    "        ),\n",
    "        # 关键修复：用纯色替代GradientColor，旧版支持\n",
    "        itemstyle_opts=opts.ItemStyleOpts(\n",
    "            color=\"#e74c3c\",  # 统一红色（醒目，突出转化主题）\n",
    "            border_color=\"#ffffff\",  # 白色边框，区分各层\n",
    "            border_width=1\n",
    "        ),\n",
    "    )\n",
    "    .set_global_opts(\n",
    "        # 标题（显示总转化情况）\n",
    "        title_opts=opts.TitleOpts(\n",
    "            title=f\"用户消费转化漏斗图\\n总注册：{total_user}人 | 最终支付：{paid_user}人\",\n",
    "            title_textstyle_opts=opts.TextStyleOpts(\n",
    "                font_size=14,\n",
    "                font_weight=\"bold\"\n",
    "            ),\n",
    "            pos_top=\"20px\"\n",
    "        ),\n",
    "        legend_opts=opts.LegendOpts(is_show=False),  # 隐藏图例（漏斗图无需）\n",
    "    )\n",
    ")\n",
    "\n",
    "# 6. 保存并提示结果\n",
    "c.render(\"29_漏斗图_用户转化漏斗.html\")\n",
    "print(\"\\n🎉 用户转化漏斗图生成完成！\")\n",
    "print(\"📁 文件：29_漏斗图_用户转化漏斗.html（双击打开即可交互）\")\n",
    "\n",
    "# 打印转化详情\n",
    "print(f\"\\n📊 转化漏斗详情：\")\n",
    "final_rate = (paid_user / total_user) * 100\n",
    "for i, (stage, count) in enumerate(funnel_data):\n",
    "    rate = (count / total_user) * 100\n",
    "    print(f\"   {i+1}. {stage}：{count}人（{rate:.1f}%）\")\n",
    "print(f\"   💡 最终支付转化率：{final_rate:.1f}%\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "961ff2df-bdf9-4411-9240-67543aa4a8e7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "✅ 成功读取用户表和消费表\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\29815\\AppData\\Local\\Temp\\ipykernel_10788\\1984513401.py:158: UserWarning:\n",
      "\n",
      "This figure includes Axes that are not compatible with tight_layout, so results might be incorrect.\n",
      "\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "🎉 综合数据大屏生成完成！\n",
      "📁 保存文件：30_消费业务综合数据大屏_Matplotlib.png\n",
      "\n",
      "📊 大屏核心信息：\n",
      "   - 总消费：613,207元 | 总订单：1,194单 | 总用户：200人\n",
      "   - 支付转化率：100.0% | TOP1消费品类：服装鞋帽\n",
      "   - 数据时间范围：2024-11 ~ 2025-11（共13个月）\n"
     ]
    }
   ],
   "source": [
    "# 1. 导入基础库（Matplotlib+Pandas，Python 默认预装）\n",
    "import matplotlib.pyplot as plt\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# 2. 关键：设置中文字体（避免中文乱码，全系统通用）\n",
    "plt.rcParams['font.sans-serif'] = ['Microsoft YaHei', 'SimHei', 'Arial Unicode MS']\n",
    "plt.rcParams['axes.unicode_minus'] = False\n",
    "\n",
    "# 3. 步骤1：读取并预处理数据（核心逻辑不变）\n",
    "try:\n",
    "    user_df = pd.read_excel('simulated_data.xlsx', sheet_name='用户表')\n",
    "    consume_df = pd.read_excel('simulated_data.xlsx', sheet_name='消费表')\n",
    "    print(\"✅ 成功读取用户表和消费表\")\n",
    "except FileNotFoundError:\n",
    "    print(\"❌ 错误：未找到simulated_data.xlsx文件！\")\n",
    "    print(\"   解决方案：\")\n",
    "    print(\"   1. 将文件放在代码同一文件夹；\")\n",
    "    print(\"   2. 或修改路径为：\")\n",
    "    print(\"      user_df = pd.read_excel(r'C:\\\\Users\\\\你的用户名\\\\Desktop\\\\simulated_data.xlsx', sheet_name='用户表')\")\n",
    "    print(\"      consume_df = pd.read_excel(r'C:\\\\Users\\\\你的用户名\\\\Desktop\\\\simulated_data.xlsx', sheet_name='消费表')\")\n",
    "    exit()\n",
    "\n",
    "# 数据预处理（过滤异常值）\n",
    "consume_df['consume_time'] = pd.to_datetime(consume_df['consume_time'], errors='coerce')\n",
    "consume_df = consume_df.dropna(subset=['consume_time'])\n",
    "consume_df['consume_month'] = consume_df['consume_time'].dt.strftime('%Y-%m')\n",
    "\n",
    "# 计算核心指标（用于指标卡片）\n",
    "total_amount = consume_df['consume_amount'].sum()          # 总消费金额\n",
    "total_order = len(consume_df)                              # 总订单数\n",
    "total_user = user_df['user_id'].nunique()                  # 总注册用户\n",
    "paid_user = consume_df['user_id'].nunique()                # 支付用户\n",
    "avg_amount = total_amount / total_order if total_order > 0 else 0  # 客单价\n",
    "category_amount = consume_df.groupby('consume_category')['consume_amount'].sum().sort_values(ascending=False)\n",
    "top_category = category_amount.index[0] if len(category_amount) > 0 else \"无数据\"\n",
    "\n",
    "# 计算月度趋势数据（折线图用）\n",
    "monthly_stats = consume_df.groupby('consume_month').agg({\n",
    "    'consume_amount': 'sum',\n",
    "    'consume_id': 'count'\n",
    "}).reset_index()\n",
    "month_list = monthly_stats['consume_month'].tolist()\n",
    "amount_list = monthly_stats['consume_amount'].tolist()\n",
    "order_list = monthly_stats['consume_id'].tolist()\n",
    "\n",
    "# 计算品类占比数据（饼图用，TOP5）\n",
    "pie_data = category_amount.head(5)\n",
    "pie_labels = pie_data.index.tolist()\n",
    "pie_values = pie_data.values.tolist()\n",
    "pie_colors = ['#3498db', '#e74c3c', '#2ecc71', '#f39c12', '#9b59b6']  # 饼图配色\n",
    "\n",
    "# 4. 步骤2：创建 Matplotlib 子图布局（2行2列，控制宽高比）\n",
    "# 设置大屏尺寸（1920x1080，适配1080P屏幕）\n",
    "fig = plt.figure(figsize=(19.2, 10.8), dpi=100)\n",
    "# 创建子图网格（2行2列，指定各子图位置和大小）\n",
    "gs = fig.add_gridspec(2, 2, \n",
    "                      width_ratios=[0.35, 0.65],  # 第1列窄（放表格/饼图），第2列宽（放折线图）\n",
    "                      height_ratios=[0.4, 0.6],   # 第1行矮（放表格），第2行高（放饼图）\n",
    "                      hspace=0.2,  # 子图垂直间距\n",
    "                      wspace=0.15  # 子图水平间距\n",
    "                     )\n",
    "\n",
    "# 5. 步骤3：添加各子图内容\n",
    "# 5.1 子图1（1行1列）：核心业务指标表格\n",
    "ax1 = fig.add_subplot(gs[0, 0])\n",
    "ax1.axis('off')  # 隐藏坐标轴\n",
    "# 创建表格数据\n",
    "table_data = [\n",
    "    ['总消费金额', f'{total_amount:,.0f} 元'],\n",
    "    ['总订单数', f'{total_order:,} 单'],\n",
    "    ['总注册用户', f'{total_user:,} 人'],\n",
    "    ['支付用户', f'{paid_user:,} 人'],\n",
    "    ['平均客单价', f'{avg_amount:.0f} 元'],\n",
    "    ['TOP1品类', top_category]\n",
    "]\n",
    "# 绘制表格\n",
    "table = ax1.table(\n",
    "    cellText=table_data,\n",
    "    colLabels=['指标名称', '数值'],\n",
    "    cellLoc='center',\n",
    "    loc='center',\n",
    "    colWidths=[0.4, 0.6]  # 列宽比例\n",
    ")\n",
    "# 美化表格\n",
    "table.auto_set_font_size(False)\n",
    "table.set_fontsize(11)\n",
    "table.scale(1, 2)  # 缩放表格（高度放大2倍）\n",
    "# 设置表头样式\n",
    "for i in range(2):\n",
    "    table[(0, i)].set_facecolor('#2c3e50')  # 表头背景色（深色）\n",
    "    table[(0, i)].set_text_props(color='white', weight='bold')\n",
    "# 设置单元格样式\n",
    "for i in range(1, len(table_data)+1):\n",
    "    for j in range(2):\n",
    "        table[(i, j)].set_facecolor('#ecf0f1')  # 单元格背景色（浅色）\n",
    "# 添加子图标题\n",
    "ax1.set_title('核心业务指标', fontsize=14, fontweight='bold', pad=20)\n",
    "\n",
    "# 5.2 子图2（1行2列）：月度消费与订单趋势折线图（双轴）\n",
    "ax2 = fig.add_subplot(gs[0, 1])\n",
    "# 左y轴：月度消费金额（蓝色）\n",
    "line1 = ax2.plot(month_list, amount_list, color='#3498db', marker='o', markersize=6, \n",
    "                 linewidth=2, label='月度消费金额（元）')\n",
    "ax2.set_xlabel('月份', fontsize=12)\n",
    "ax2.set_ylabel('消费金额（元）', color='#3498db', fontsize=12)\n",
    "ax2.tick_params(axis='y', labelcolor='#3498db')\n",
    "ax2.tick_params(axis='x', rotation=45)  # 旋转x轴标签，避免重叠\n",
    "# 创建右y轴（共享x轴）\n",
    "ax2_twin = ax2.twinx()\n",
    "# 右y轴：月度订单数（红色）\n",
    "line2 = ax2_twin.plot(month_list, order_list, color='#e74c3c', marker='s', markersize=6, \n",
    "                      linewidth=2, label='月度订单数')\n",
    "ax2_twin.set_ylabel('订单数', color='#e74c3c', fontsize=12)\n",
    "ax2_twin.tick_params(axis='y', labelcolor='#e74c3c')\n",
    "# 合并图例\n",
    "lines = line1 + line2\n",
    "labels = [l.get_label() for l in lines]\n",
    "ax2.legend(lines, labels, loc='upper left', fontsize=10)\n",
    "# 添加网格\n",
    "ax2.grid(True, alpha=0.3, linestyle='--')\n",
    "# 添加子图标题\n",
    "ax2.set_title('月度消费与订单趋势', fontsize=14, fontweight='bold', pad=20)\n",
    "\n",
    "# 5.3 子图3（2行1列）：TOP5消费品类占比饼图\n",
    "ax3 = fig.add_subplot(gs[1, 0])\n",
    "# 绘制环形饼图（wedgeprops控制内环大小）\n",
    "wedges, texts, autotexts = ax3.pie(\n",
    "    pie_values,\n",
    "    labels=pie_labels,\n",
    "    colors=pie_colors,\n",
    "    autopct='%1.1f%%',  # 显示百分比（1位小数）\n",
    "    startangle=90,\n",
    "    wedgeprops=dict(width=0.4)  # 环形宽度（0.4表示内环半径占外环40%）\n",
    ")\n",
    "# 美化饼图文本\n",
    "for autotext in autotexts:\n",
    "    autotext.set_color('white')\n",
    "    autotext.set_fontweight('bold')\n",
    "    autotext.set_fontsize(10)\n",
    "for text in texts:\n",
    "    text.set_fontsize(11)\n",
    "# 添加子图标题\n",
    "ax3.set_title('TOP5消费品类占比', fontsize=14, fontweight='bold', pad=20)\n",
    "\n",
    "# 5.4 子图4（2行2列）：占位（可后续扩展，如用户地域分布）\n",
    "ax4 = fig.add_subplot(gs[1, 1])\n",
    "ax4.axis('off')  # 隐藏坐标轴\n",
    "# 添加占位文本（提示可扩展）\n",
    "ax4.text(0.5, 0.5, '可扩展组件\\n（如用户地域分布、转化漏斗等）', \n",
    "         ha='center', va='center', fontsize=12, color='#999',\n",
    "         bbox=dict(boxstyle='round,pad=1', facecolor='#f8f9fa', edgecolor='#ddd'))\n",
    "\n",
    "# 6. 步骤4：添加大屏总标题\n",
    "fig.suptitle('消费业务综合数据大屏', fontsize=20, fontweight='bold', y=0.95)\n",
    "\n",
    "# 7. 步骤5：保存大屏为高分辨率图片（PNG格式，可直接插入报告/PPT）\n",
    "plt.tight_layout()  # 自动调整布局，避免标签重叠\n",
    "plt.subplots_adjust(top=0.92)  # 预留顶部空间给总标题\n",
    "plt.savefig(\n",
    "    '30_消费业务综合数据大屏_Matplotlib.png',\n",
    "    dpi=300,  # 高分辨率（300dpi，适合印刷/电子展示）\n",
    "    bbox_inches='tight',  # 紧凑布局，避免边缘裁剪\n",
    "    facecolor='white'     # 背景色为白色\n",
    ")\n",
    "plt.close()\n",
    "\n",
    "# 8. 打印结果提示\n",
    "print(\"\\n🎉 综合数据大屏生成完成！\")\n",
    "print(\"📁 保存文件：30_消费业务综合数据大屏_Matplotlib.png\")\n",
    "print(\"\\n📊 大屏核心信息：\")\n",
    "print(f\"   - 总消费：{total_amount:,.0f}元 | 总订单：{total_order:,}单 | 总用户：{total_user:,}人\")\n",
    "print(f\"   - 支付转化率：{paid_user/total_user*100:.1f}% | TOP1消费品类：{top_category}\")\n",
    "print(f\"   - 数据时间范围：{month_list[0]} ~ {month_list[-1]}（共{len(month_list)}个月）\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e4402f94-2df4-4646-a940-09f29b4467ca",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7ebab4e8-217e-488b-b85c-136cfef37785",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
